================================================================================
  [GRAPHIQUE] DataInsight Pro — SQL + Python
  PARTIE 1 — SETUP DU PROJET & BASE DE DONNÉES RELATIONNELLE
================================================================================

"SQL est la langue maternelle de la donnée. Python est son interprète."

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
1⃣  CONTEXTE MÉTIER RÉEL
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

ENTREPRISE : EuroShop Commerce S.A.S.
Secteur    : E-commerce B2C et B2B européen
Données    : Système de gestion des commandes (OMS) + CRM

Le DSI (Directeur des Systèmes d'Information) vous convoque :

  "Nos données sont dispersées entre notre ERP (SAP), notre CRM (Salesforce)
   et notre logistique (WMS). Nous les avons consolidées dans une base SQL.
   Votre mission : la comprendre, l'interroger et en extraire de la valeur."

PROBLÉMATIQUE :
  -> 57 293 enregistrements dans 11 tables relationnelles
  -> Données 2022-2023 (2 années complètes)
  -> Besoin : pipeline Python + SQL automatisé

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
2⃣  OBJECTIFS PÉDAGOGIQUES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  [OK] Comprendre un schéma relationnel (tables, clés, relations)
  [OK] Installer et configurer SQLite avec Python
  [OK] Créer un module de connexion réutilisable
  [OK] Exécuter les premières requêtes SQL d'exploration
  [OK] Charger des données SQL dans pandas DataFrame

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
3⃣  BASE DE DONNÉES : real_database.db / real_database.sql
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

SOURCE   : Inspiré du UCI Online Retail II (open data réel)
           -> https://archive.ics.uci.edu/dataset/502/online+retail+ii
FORMAT   : SQLite 3 (portable, sans serveur)
FICHIERS :
  data/real_database.db  -> Base compilée (binaire, ~5.5 Mo)
  data/real_database.sql -> Dump SQL lisible (~5 Mo)

POUR IMPORTER SUR POSTGRESQL/MYSQL (entreprise) :
  psql -U user -d ma_base < real_database.sql
  mysql -u root -p ma_base < real_database.sql

────────────────────────────────────────────────────────────────────────────────
SCHÉMA RELATIONNEL COMPLET — 11 TABLES
────────────────────────────────────────────────────────────────────────────────

┌─────────────────────────────────────────────────────────────────────────────┐
│                    DIAGRAMME ENTITÉ-RELATION (ERD)                          │
│                                                                             │
│  regions ──┐                                                                │
│  (30)      │ 1:N                                                            │
│            ├──-> customers ──-> orders ──-> order_items ──-> products           │
│            │    (2500)     (12000)   (26063)          (64)                  │
│            │                  │                          │                  │
│            │                  ├──-> payments (12000)      │                  │
│            │                  ├──-> reviews (3494)        │                  │
│            │                  └──-> returns (628)         │                  │
│  categories ──────────────────────────────────────────->─┘                  │
│  (8)        1:N                                          inventory_log(500) │
│                                                                             │
│  payment_methods ──-> orders                                                 │
│  (6)                                                                        │
└─────────────────────────────────────────────────────────────────────────────┘

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
DESCRIPTION DÉTAILLÉE DES 11 TABLES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

TABLE 1 : regions (30 lignes)
────────────────────────────────────────────────────────────────────────────
  Rôle : Table de référence géographique. Contient les pays et régions
         où opère EuroShop Commerce.

  Colonnes :
  ┌────────────────┬──────────────┬──────────────────────────────────────────┐
  │ region_id      │ INTEGER PK   │ Identifiant auto-incrémenté              │
  │ country        │ TEXT NOT NULL│ Pays (France, Allemagne...)              │
  │ region_name    │ TEXT NOT NULL│ Région administrative                    │
  │ timezone       │ TEXT         │ Fuseau horaire (Europe/Paris...)         │
  │ currency       │ TEXT         │ Devise (EUR, GBP, CHF...)                │
  └────────────────┴──────────────┴──────────────────────────────────────────┘

  Relations :
    -> PK : region_id
    -> Référencée par : customers.region_id (FK), orders.region_id (FK)
    -> Cardinalité : 1 région -> N clients, 1 région -> N commandes

TABLE 2 : customers (2 500 lignes)
────────────────────────────────────────────────────────────────────────────
  Rôle : Table centrale des clients. Données démographiques et commerciales.

  Colonnes :
  ┌────────────────┬──────────────┬──────────────────────────────────────────┐
  │ customer_id    │ TEXT PK      │ Identifiant unique (CUST-0001...)        │
  │ first_name     │ TEXT NOT NULL│ Prénom                                   │
  │ last_name      │ TEXT NOT NULL│ Nom de famille                           │
  │ email          │ TEXT UNIQUE  │ Email (unicité garantie)                 │
  │ phone          │ TEXT         │ Numéro de téléphone                      │
  │ birth_date     │ TEXT         │ Date de naissance (YYYY-MM-DD)           │
  │ gender         │ TEXT         │ M / F / Autre (contrainte CHECK)         │
  │ segment        │ TEXT         │ Particulier / PME / Grande entr. / Admin │
  │ region_id      │ INTEGER FK   │ -> regions.region_id                      │
  │ signup_date    │ TEXT NOT NULL│ Date d'inscription                       │
  │ is_active      │ INTEGER      │ 1=actif, 0=inactif (CHECK 0 ou 1)        │
  └────────────────┴──────────────┴──────────────────────────────────────────┘

  Relations :
    -> PK : customer_id
    -> FK : region_id -> regions.region_id
    -> Référencée par : orders.customer_id
    -> Cardinalité : 1 client -> N commandes

TABLE 3 : categories (8 lignes)
────────────────────────────────────────────────────────────────────────────
  Rôle : Hiérarchie des catégories de produits (auto-référentielle possible).

  Colonnes :
  ┌────────────────┬──────────────┬──────────────────────────────────────────┐
  │ category_id    │ INTEGER PK   │ Identifiant auto-incrémenté              │
  │ name           │ TEXT UNIQUE  │ Nom de la catégorie                      │
  │ description    │ TEXT         │ Description métier                       │
  │ parent_id      │ INTEGER FK   │ -> categories.category_id (sous-catégo.)  │
  └────────────────┴──────────────┴──────────────────────────────────────────┘

TABLE 4 : products (64 lignes)
────────────────────────────────────────────────────────────────────────────
  Rôle : Catalogue produits avec prix de vente ET coût d'achat.
         Permet de calculer la marge brute.

  Colonnes :
  ┌────────────────┬──────────────┬──────────────────────────────────────────┐
  │ product_id     │ TEXT PK      │ Code produit (ELEC-001, VETE-002...)     │
  │ name           │ TEXT NOT NULL│ Nom complet du produit                   │
  │ category_id    │ INTEGER FK   │ -> categories.category_id                 │
  │ unit_price     │ REAL         │ Prix de vente HT (CHECK > 0)             │
  │ cost_price     │ REAL         │ Prix d'achat HT (CHECK > 0)              │
  │ stock_qty      │ INTEGER      │ Quantité en stock actuelle               │
  │ weight_kg      │ REAL         │ Poids en kg (pour calcul frais port)     │
  │ is_active      │ INTEGER      │ 1=en vente, 0=discontinué                │
  └────────────────┴──────────────┴──────────────────────────────────────────┘

  Colonne métier clé :
    marge_brute = unit_price - cost_price
    pct_marge   = (unit_price - cost_price) / unit_price × 100

TABLE 5 : payment_methods (6 lignes)
────────────────────────────────────────────────────────────────────────────
  Rôle : Table de référence des modes de paiement acceptés.

  Colonnes :
  ┌────────────────┬──────────────┬──────────────────────────────────────────┐
  │ method_id      │ INTEGER PK   │ Auto-incrémenté                          │
  │ name           │ TEXT UNIQUE  │ Carte de crédit / PayPal / etc.          │
  │ type           │ TEXT         │ card / digital / transfer / cash         │
  └────────────────┴──────────────┴──────────────────────────────────────────┘

TABLE 6 : orders (12 000 lignes)  <- TABLE CENTRALE
────────────────────────────────────────────────────────────────────────────
  Rôle : Table des commandes. Pivot central du schéma.
         TOUTES les analyses commencent ici.

  Colonnes :
  ┌────────────────┬──────────────┬──────────────────────────────────────────┐
  │ order_id       │ TEXT PK      │ Identifiant commande (ORD-536365...)     │
  │ customer_id    │ TEXT FK      │ -> customers.customer_id                  │
  │ order_date     │ TEXT NOT NULL│ Date commande (YYYY-MM-DD)               │
  │ status         │ TEXT         │ Livré/En cours/Annulé/Retourné/En attente│
  │ method_id      │ INTEGER FK   │ -> payment_methods.method_id              │
  │ region_id      │ INTEGER FK   │ -> regions.region_id (livraison)          │
  │ shipping_fee   │ REAL         │ Frais de port (€)                        │
  │ notes          │ TEXT         │ Notes libres                             │
  └────────────────┴──────────────┴──────────────────────────────────────────┘

TABLE 7 : order_items (26 063 lignes)  <- TABLE LA PLUS VOLUMINEUSE
────────────────────────────────────────────────────────────────────────────
  Rôle : Détail des produits commandés par commande (table de liaison).
         1 commande -> N lignes de commande.

  Colonnes :
  ┌────────────────┬──────────────┬──────────────────────────────────────────┐
  │ item_id        │ INTEGER PK   │ Auto-incrémenté                          │
  │ order_id       │ TEXT FK      │ -> orders.order_id                        │
  │ product_id     │ TEXT FK      │ -> products.product_id                    │
  │ quantity       │ INTEGER      │ Quantité commandée (CHECK > 0)           │
  │ unit_price     │ REAL         │ Prix au moment de la commande            │
  │ discount_pct   │ REAL         │ Remise appliquée (0.0 à 1.0)             │
  └────────────────┴──────────────┴──────────────────────────────────────────┘

  Calcul du CA ligne :
    ca_ligne = quantity × unit_price × (1 - discount_pct)

TABLE 8 : payments (12 000 lignes)
────────────────────────────────────────────────────────────────────────────
  Rôle : Transactions financières. 1 commande = 1 paiement (simplifié).

  Colonnes :
  ┌────────────────┬──────────────┬──────────────────────────────────────────┐
  │ payment_id     │ INTEGER PK   │ Auto-incrémenté                          │
  │ order_id       │ TEXT FK      │ -> orders.order_id                        │
  │ amount         │ REAL         │ Montant total payé                       │
  │ paid_date      │ TEXT         │ Date du paiement                         │
  │ status         │ TEXT         │ Validé/En attente/Remboursé/Échoué       │
  │ reference      │ TEXT UNIQUE  │ Référence unique du paiement             │
  └────────────────┴──────────────┴──────────────────────────────────────────┘

TABLE 9 : reviews (3 494 lignes)
────────────────────────────────────────────────────────────────────────────
  Rôle : Avis clients par produit/commande. Donnée qualitative cruciale.

  Colonnes :
  ┌────────────────┬──────────────┬──────────────────────────────────────────┐
  │ review_id      │ INTEGER PK   │ Auto-incrémenté                          │
  │ order_id       │ TEXT FK      │ -> orders.order_id                        │
  │ product_id     │ TEXT FK      │ -> products.product_id                    │
  │ rating         │ INTEGER      │ Note de 1 à 5 (CHECK 1-5)                │
  │ comment        │ TEXT         │ Commentaire libre (peut être NULL)       │
  │ review_date    │ TEXT         │ Date de l'avis                           │
  └────────────────┴──────────────┴──────────────────────────────────────────┘

TABLE 10 : returns (628 lignes)
────────────────────────────────────────────────────────────────────────────
  Rôle : Suivi des retours produits.

  Colonnes :
  ┌────────────────┬──────────────┬──────────────────────────────────────────┐
  │ return_id      │ INTEGER PK   │ Auto-incrémenté                          │
  │ order_id       │ TEXT FK      │ -> orders.order_id                        │
  │ product_id     │ TEXT FK      │ -> products.product_id                    │
  │ quantity       │ INTEGER      │ Quantité retournée (CHECK > 0)           │
  │ reason         │ TEXT         │ Défectueux/Mauvaise taille/etc.          │
  │ return_date    │ TEXT         │ Date du retour                           │
  │ refund_amount  │ REAL         │ Montant remboursé                        │
  └────────────────┴──────────────┴──────────────────────────────────────────┘

TABLE 11 : inventory_log (500 lignes)
────────────────────────────────────────────────────────────────────────────
  Rôle : Journal des mouvements de stock (entrées/sorties).

  Colonnes :
  ┌────────────────┬──────────────┬──────────────────────────────────────────┐
  │ log_id         │ INTEGER PK   │ Auto-incrémenté                          │
  │ product_id     │ TEXT FK      │ -> products.product_id                    │
  │ change_qty     │ INTEGER      │ Variation (positif=entrée, négatif=vente)│
  │ reason         │ TEXT         │ Vente/Réapprovisionnement/Casse/Ajust.   │
  │ log_date       │ TEXT         │ Date du mouvement                        │
  └────────────────┴──────────────┴──────────────────────────────────────────┘

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
4⃣  THÉORIE — CONCEPTS FONDAMENTAUX SQL ET BASES RELATIONNELLES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

CLÉS PRIMAIRES (PRIMARY KEY — PK) :
  -> Identifie UNIQUEMENT chaque ligne d'une table
  -> Ne peut jamais être NULL
  -> Deux types :
    Naturelle  : customer_id TEXT ('CUST-0001') — déjà signifiant
    Artificielle: region_id INTEGER AUTOINCREMENT — généré par la base

CLÉS ÉTRANGÈRES (FOREIGN KEY — FK) :
  -> Crée une RELATION entre deux tables
  -> orders.customer_id -> customers.customer_id
  -> Garantit l'intégrité référentielle :
    On ne peut pas créer une commande pour un client inexistant
  -> Activer avec : PRAGMA foreign_keys = ON;

CARDINALITÉS :
  1:1  -> Un client a UN seul profil (peu commun)
  1:N  -> Un client a PLUSIEURS commandes (le plus courant)
  N:M  -> Une commande a PLUSIEURS produits, un produit dans PLUSIEURS commandes
         -> Résolu par une table de liaison : order_items

TYPES SQL PRINCIPAUX :
  INTEGER -> entier (4 ou 8 octets)
  REAL    -> nombre décimal (8 octets, float64)
  TEXT    -> chaîne de caractères (longueur variable)
  BLOB    -> données binaires (images, fichiers)
  NULL    -> absence de valeur (différent de 0 ou '')

CONTRAINTES SQL :
  NOT NULL        -> La valeur ne peut pas être NULL
  UNIQUE          -> Chaque valeur doit être unique dans la colonne
  CHECK(condition)-> Condition custom (ex: CHECK(rating BETWEEN 1 AND 5))
  DEFAULT valeur  -> Valeur par défaut si non renseignée
  PRIMARY KEY     -> Combinaison de UNIQUE + NOT NULL + index automatique
  REFERENCES      -> Clé étrangère

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
5⃣  IMPLÉMENTATION — SETUP COMPLET DU PROJET
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

──────────────────────────────────────────────────
ARCHITECTURE DU PROJET
──────────────────────────────────────────────────

python_data_project/         <- Racine du projet
│
├── data/
│   ├── real_database.db     <- Base SQLite (binaire)
│   └── real_database.sql    <- Dump SQL (texte lisible)
│
├── notebooks/
│   └── exploration.ipynb    <- Exploration interactive
│
├── src/
│   ├── __init__.py
│   ├── db_connection.py     <- Connexion SQLite réutilisable
│   ├── queries.sql          <- Requêtes SQL organisées
│   ├── data_loader.py       <- Charge SQL -> DataFrame pandas
│   ├── data_cleaning.py     <- Nettoyage des résultats
│   ├── analysis.py          <- Calculs et KPIs
│   ├── visualization.py     <- Graphiques
│   └── utils.py             <- Utilitaires
│
├── reports/
│   └── final_report.md
│
├── requirements.txt
├── .env                     <- Variables d'environnement (chemin DB)
└── main.py

──────────────────────────────────────────────────
requirements.txt
──────────────────────────────────────────────────

```
# DataInsight Pro SQL — Dépendances
# Python 3.11+

# ── Core data ──
pandas==2.1.4
numpy==1.26.2

# ── Base de données ──
# sqlite3 est INCLUS dans Python standard (pas à installer)
# Pour PostgreSQL : psycopg2-binary==2.9.9
# Pour MySQL      : pymysql==1.1.0

# ── Visualisation ──
matplotlib==3.8.2
seaborn==0.13.0
plotly==5.18.0

# ── Statistiques ──
scipy==1.11.4
scikit-learn==1.3.2

# ── Utilitaires ──
python-dotenv==1.0.0
loguru==0.7.2
tabulate==0.9.0    # Affichage de tableaux dans le terminal
```

──────────────────────────────────────────────────
.env — Variables d'environnement
──────────────────────────────────────────────────

```bash
# .env — Configuration de l'environnement
# NE PAS COMMITER CE FICHIER dans git
# Ajouter .env dans .gitignore

# Chemin de la base de données
DB_PATH=data/real_database.db

# Paramètres d'analyse
ANALYSE_DATE_DEBUT=2022-01-01
ANALYSE_DATE_FIN=2023-12-31
MARGE_BRUTE_CIBLE=0.35
```

──────────────────────────────────────────────────
src/db_connection.py — MODULE DE CONNEXION
──────────────────────────────────────────────────

```python
# src/db_connection.py
"""
Module de connexion à la base de données SQLite.

Responsabilité UNIQUE : gérer la connexion à la base.
  - Ouvrir la connexion
  - Exécuter les requêtes
  - Fermer proprement

Principe Context Manager :
  Utiliser `with DatabaseConnection(...) as db:` garantit que la
  connexion est toujours fermée, même en cas d'erreur.

Compatibilité :
  SQLite  -> sqlite3 (standard Python)
  PostgreSQL -> psycopg2 (même interface, adapter la classe)
  MySQL   -> pymysql (même interface)
"""

import sqlite3            # Module standard Python — pas à installer
import pandas as pd       # Pour pd.read_sql_query()
import logging            # Système de logs
from pathlib import Path  # Chemins cross-platform
from typing import Optional, List, Tuple, Any, Union
import os                 # Pour lire les variables d'environnement

logger = logging.getLogger("DataInsightPro")


class DatabaseConnection:
    """
    Gestionnaire de connexion SQLite avec support context manager.

    Usage minimal :
        with DatabaseConnection("data/real_database.db") as db:
            df = db.query_df("SELECT * FROM customers LIMIT 10")

    Usage avancé :
        db = DatabaseConnection("data/real_database.db")
        db.connect()
        df = db.query_df("SELECT COUNT(*) FROM orders")
        db.disconnect()

    Attributs :
        chemin_db  : chemin vers le fichier .db
        conn       : objet connexion sqlite3 (None si pas connecté)
        cursor     : objet cursor sqlite3
        _requetes  : compteur de requêtes exécutées (debug)
    """

    def __init__(self, chemin_db: str = None):
        """
        Initialise le gestionnaire de connexion.

        Paramètre :
            chemin_db : chemin vers real_database.db
                        Si None -> lit la variable d'env DB_PATH
        """
        # Si chemin_db non fourni -> lire depuis .env ou variable d'env
        if chemin_db is None:
            chemin_db = os.getenv("DB_PATH", "data/real_database.db")

        # Path() -> objet chemin cross-platform (fonctionne sur Windows et Linux)
        self.chemin_db = Path(chemin_db)

        # Attributs de connexion — None avant connect()
        self.conn: Optional[sqlite3.Connection] = None
        self.cursor: Optional[sqlite3.Cursor] = None

        # Compteur pour le debug
        self._requetes_executees: int = 0

        logger.debug(f"DatabaseConnection initialisé -> {self.chemin_db}")

    def connect(self) -> None:
        """
        Ouvre la connexion à la base de données.

        Lève FileNotFoundError si le fichier .db n'existe pas.
        """
        if not self.chemin_db.exists():
            raise FileNotFoundError(
                f"Base de données introuvable : {self.chemin_db}\n"
                f"Vérifiez que real_database.db est dans data/"
            )

        # sqlite3.connect() -> ouvre la connexion au fichier .db
        # check_same_thread=False -> permet l'utilisation dans des threads
        self.conn = sqlite3.connect(
            str(self.chemin_db),
            check_same_thread=False
        )

        # .row_factory -> format des résultats
        # sqlite3.Row -> accès par nom de colonne : row["customer_id"]
        # Par défaut : tuple indexé : row[0]
        self.conn.row_factory = sqlite3.Row

        # Créer le cursor (objet pour exécuter les requêtes)
        self.cursor = self.conn.cursor()

        # Activer les clés étrangères (désactivées par défaut dans SQLite !)
        self.cursor.execute("PRAGMA foreign_keys = ON;")

        # Mode WAL (Write-Ahead Logging) -> meilleures performances lecture
        self.cursor.execute("PRAGMA journal_mode = WAL;")

        logger.info(f"[OK] Connexion ouverte : {self.chemin_db.name}")

    def disconnect(self) -> None:
        """Ferme proprement la connexion."""
        if self.conn:
            self.conn.close()
            self.conn = None
            self.cursor = None
            logger.debug("Connexion fermée")

    # ── Context Manager ──────────────────────────────────────────
    def __enter__(self) -> "DatabaseConnection":
        """
        Appelé automatiquement par `with DatabaseConnection(...) as db:`
        Ouvre la connexion et retourne l'objet.
        """
        self.connect()
        return self    # `db` dans `with ... as db:` = cet objet

    def __exit__(self, exc_type, exc_val, exc_tb) -> None:
        """
        Appelé automatiquement en fin de bloc `with`.
        Ferme la connexion MÊME si une exception a été levée.

        Paramètres :
            exc_type : type de l'exception (None si pas d'exception)
            exc_val  : valeur de l'exception
            exc_tb   : traceback
        """
        self.disconnect()
        # Retourner False -> ne pas supprimer l'exception si elle existe
        return False

    # ── Méthode 1 : Requête -> DataFrame ────────────────────────────
    def query_df(self, sql: str,
                  params: Optional[Union[tuple, dict]] = None) -> pd.DataFrame:
        """
        Exécute une requête SELECT et retourne un DataFrame pandas.

        C'est la méthode principale pour les analyses.

        Paramètres :
            sql    : requête SQL (peut contenir des ? ou :nom)
            params : paramètres à substituer (protection SQL injection)

        Retourne :
            pd.DataFrame avec les résultats

        Exemples :
            # Simple
            df = db.query_df("SELECT * FROM customers LIMIT 10")

            # Avec paramètres (tuple)
            df = db.query_df(
                "SELECT * FROM orders WHERE status = ?",
                ("Livré",)
            )

            # Avec paramètres nommés (dict)
            df = db.query_df(
                "SELECT * FROM orders WHERE status = :s AND order_date > :d",
                {"s": "Livré", "d": "2022-06-01"}
            )
        """
        if self.conn is None:
            raise RuntimeError("Connexion non ouverte. Utiliser with DatabaseConnection() as db:")

        # pd.read_sql_query() -> exécute SQL + retourne DataFrame directement
        # params=None si pas de paramètres (pd.read_sql_query l'accepte)
        df = pd.read_sql_query(sql, self.conn, params=params)

        self._requetes_executees += 1
        logger.debug(f"Requête #{self._requetes_executees} : {len(df)} lignes retournées")

        return df

    # ── Méthode 2 : Requête simple ─────────────────────────────────
    def execute(self, sql: str,
                 params: Optional[tuple] = None) -> sqlite3.Cursor:
        """
        Exécute une requête SQL (INSERT, UPDATE, DELETE, CREATE...).
        Pour les SELECT, préférer query_df().

        Paramètre :
            sql    : requête SQL
            params : paramètres (tuple)

        Retourne :
            sqlite3.Cursor (pour accéder à lastrowid etc.)
        """
        if params:
            self.cursor.execute(sql, params)
        else:
            self.cursor.execute(sql)

        # commit() -> valide les modifications (obligatoire pour INSERT/UPDATE/DELETE)
        self.conn.commit()
        return self.cursor

    # ── Méthode 3 : Infos sur la base ──────────────────────────────
    def lister_tables(self) -> List[str]:
        """
        Retourne la liste des tables de la base.

        sqlite_master -> table système SQLite qui contient le schéma
        """
        df = self.query_df(
            "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
        )
        return df["name"].tolist()

    def infos_table(self, nom_table: str) -> pd.DataFrame:
        """
        Retourne les informations sur les colonnes d'une table.

        PRAGMA table_info -> commande SQLite pour le schéma d'une table
        Retourne : cid, name, type, notnull, dflt_value, pk
        """
        return self.query_df(f"PRAGMA table_info({nom_table})")

    def compter_lignes(self) -> pd.DataFrame:
        """Retourne le nombre de lignes de chaque table."""
        tables = self.lister_tables()
        resultats = []
        for table in tables:
            count = self.query_df(f"SELECT COUNT(*) as n FROM {table}")["n"].iloc[0]
            resultats.append({"table": table, "lignes": count})
        return pd.DataFrame(resultats).sort_values("lignes", ascending=False)

    def taille_db(self) -> str:
        """Retourne la taille du fichier de base de données."""
        size_bytes = self.chemin_db.stat().st_size
        if size_bytes < 1024:
            return f"{size_bytes} o"
        elif size_bytes < 1024**2:
            return f"{size_bytes/1024:.1f} Ko"
        else:
            return f"{size_bytes/1024**2:.1f} Mo"

    def afficher_schema(self) -> None:
        """Affiche le schéma complet de la base dans le terminal."""
        print(f"\n{'═'*60}")
        print(f"  SCHÉMA : {self.chemin_db.name} ({self.taille_db()})")
        print(f"{'═'*60}")

        df_count = self.compter_lignes()
        for _, row in df_count.iterrows():
            table = row["table"]
            n = row["lignes"]
            cols = self.infos_table(table)
            pk_col = cols[cols["pk"] > 0]["name"].tolist()
            fk_cols = []   # SQLite n'expose pas les FK directement dans PRAGMA

            print(f"\n  [LISTE] {table} ({n:,} lignes)")
            for _, col in cols.iterrows():
                pk_marker = " [PK]" if col["pk"] > 0 else ""
                nn_marker = " NOT NULL" if col["notnull"] else ""
                print(f"     {col['name']:20s} {col['type']:10s}{pk_marker}{nn_marker}")
```

──────────────────────────────────────────────────
src/__init__.py
──────────────────────────────────────────────────

```python
# src/__init__.py
"""
DataInsight Pro SQL — Package source.
"""
__version__ = "2.0.0"
__author__  = "DataInsight Pro Team"
```

──────────────────────────────────────────────────
PREMIÈRE UTILISATION — Test de connexion
──────────────────────────────────────────────────

```python
# test_connexion.py — À exécuter pour vérifier l'installation

from src.db_connection import DatabaseConnection

# Test basique
with DatabaseConnection("data/real_database.db") as db:

    # 1. Afficher le schéma complet
    db.afficher_schema()

    # 2. Lister les tables
    tables = db.lister_tables()
    print(f"\nTables : {tables}")

    # 3. Première requête SQL
    df = db.query_df("SELECT COUNT(*) as total FROM orders")
    print(f"\nNombre de commandes : {df['total'].iloc[0]:,}")

    # 4. Aperçu clients
    df_clients = db.query_df("SELECT * FROM customers LIMIT 5")
    print(f"\n5 premiers clients :")
    print(df_clients[["customer_id","first_name","last_name","segment","signup_date"]])
```

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
6⃣  EXPLICATION LIGNE PAR LIGNE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

SQL :
```sql
CREATE TABLE customers (
    customer_id TEXT PRIMARY KEY,
    region_id   INTEGER REFERENCES regions(region_id)
);
```
  CREATE TABLE customers -> créer une table nommée "customers"
  ( ... )               -> définition des colonnes entre parenthèses
  customer_id           -> nom de la colonne
  TEXT                  -> type de données (chaîne de caractères)
  PRIMARY KEY           -> clé primaire (unique + non null + index)
  INTEGER               -> type entier
  REFERENCES regions(region_id) -> clé étrangère : cette valeur DOIT
                                   exister dans regions.region_id

```sql
PRAGMA foreign_keys = ON;
```
  PRAGMA      -> directive de configuration SQLite
  foreign_keys = ON -> activer les contraintes de clés étrangères
  OBLIGATOIRE dans SQLite ! Désactivées par défaut pour compatibilité.

```sql
CREATE INDEX idx_orders_customer ON orders(customer_id);
```
  CREATE INDEX -> créer un index
  idx_orders_customer -> nom de l'index (convention : idx_table_colonne)
  ON orders(customer_id) -> sur la table orders, colonne customer_id
  Effet : accélère les requêtes WHERE customer_id = ? et les JOINs

PYTHON :
```python
self.conn = sqlite3.connect(str(self.chemin_db))
```
  sqlite3.connect()  -> ouvre la connexion au fichier .db
  str(self.chemin_db)-> convertir Path en string (requis par sqlite3)
  Retourne           -> objet Connection

```python
df = pd.read_sql_query(sql, self.conn, params=params)
```
  pd.read_sql_query() -> exécute SQL + retourne DataFrame
  sql                 -> la requête SELECT
  self.conn           -> connexion active
  params              -> paramètres protégés (évite SQL injection)
  Avantage vs cursor.execute() -> retourne directement un DataFrame pandas

```python
def __enter__(self) -> "DatabaseConnection":
    self.connect()
    return self
```
  __enter__ -> méthode magique appelée par `with ... as db:`
  return self -> la variable `db` dans `with ... as db:` = cet objet
  Permet d'écrire : with DatabaseConnection("...") as db:
  -> Garantit que la connexion est ouverte avant d'entrer dans le bloc

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
8⃣  BONNES PRATIQUES SQL
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[OK] TOUJOURS activer les clés étrangères dans SQLite
   PRAGMA foreign_keys = ON;  <- sans ça, les FK ne sont pas vérifiées !

[OK] TOUJOURS utiliser des paramètres pour les valeurs variables
   [X] f"SELECT * FROM orders WHERE status = '{statut}'"  -> SQL injection !
   [OK] db.query_df("SELECT * FROM orders WHERE status = ?", (statut,))

[OK] NOMMER les index de manière explicite
   idx_table_colonne  -> idx_orders_customer_id

[OK] UTILISER des types précis
   INTEGER  pour les entiers (jamais FLOAT pour des IDs)
   REAL     pour les prix, pourcentages (précision float64)
   TEXT     pour les dates dans SQLite (format ISO 'YYYY-MM-DD')

[OK] DOCUMENTER les relations dans les commentaires SQL
   -- FK: orders.customer_id -> customers.customer_id (1:N)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
9⃣  ERREURS FRÉQUENTES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

ERREUR 1 : Oublier PRAGMA foreign_keys
  [X]  conn = sqlite3.connect("db.db")
      # Les FK ne sont PAS vérifiées -> on peut insérer des orphelins !

  [OK]  conn = sqlite3.connect("db.db")
      conn.execute("PRAGMA foreign_keys = ON;")

ERREUR 2 : Connexion non fermée
  [X]  conn = sqlite3.connect("db.db")
      df = pd.read_sql_query(sql, conn)
      # Si exception -> connexion reste ouverte = fuite de ressource

  [OK]  with DatabaseConnection("db.db") as db:
      df = db.query_df(sql)
      # Connexion TOUJOURS fermée grâce à __exit__

ERREUR 3 : Type TEXT pour les dates dans SQLite
  SQLite n'a pas de type DATE natif -> stocker comme TEXT en format ISO
  '2022-01-15'  <- correct (ISO 8601)
  '15/01/2022'  <- incorrect (tri alphabétique incorrect)
  Utiliser : strftime() pour formater

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[10]  EXERCICES PARTIE 1
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

FACILE :
  Ex 1.1 : Créez l'architecture complète du projet (dossiers + fichiers vides).
  Ex 1.2 : Connectez-vous à la base et affichez le nombre de lignes par table.
  Ex 1.3 : Affichez le schéma de la table orders (colonnes, types, PK).

INTERMÉDIAIRE :
  Ex 1.4 : Ajoutez une méthode `executer_script_sql(fichier)` à DatabaseConnection
           qui lit et exécute un fichier .sql complet.
  Ex 1.5 : Ajoutez un système de connexion avec retry (3 tentatives si échec).

AVANCÉ :
  Ex 1.6 : Créez un adaptateur qui supporte SQLite ET PostgreSQL avec la même
           interface (pattern Strategy/Adapter).

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
CORRIGÉS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

```python
# ── Corrigé Ex 1.2 ───────────────────────────────────────────────
from src.db_connection import DatabaseConnection

with DatabaseConnection("data/real_database.db") as db:
    df = db.compter_lignes()
    print(df.to_string(index=False))

# ── Corrigé Ex 1.4 ───────────────────────────────────────────────
def executer_script_sql(self, chemin_sql: str) -> None:
    """
    Lit et exécute un fichier .sql complet.
    Utile pour les migrations et initialisations.
    """
    from pathlib import Path
    chemin = Path(chemin_sql)
    if not chemin.exists():
        raise FileNotFoundError(f"Fichier SQL introuvable : {chemin}")

    # .read_text() -> lit tout le fichier comme chaîne
    script = chemin.read_text(encoding="utf-8")

    # .executescript() -> exécute plusieurs instructions SQL d'un coup
    self.conn.executescript(script)
    self.conn.commit()
    print(f"[OK] Script exécuté : {chemin.name}")
```

================================================================================
FIN PARTIE 1 — Prochaine : Partie 2 — Requêtes SQL fondamentales
================================================================================


================================================================================
  [GRAPHIQUE] DataInsight Pro SQL — PARTIE 2 — REQUÊTES SQL FONDAMENTALES
================================================================================

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
5⃣  src/queries.sql — BIBLIOTHÈQUE DE REQUÊTES FONDAMENTALES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

```sql
-- ==========================================================================
-- queries.sql — Bibliothèque de requêtes EuroShop Commerce
-- Utilisation : lire les sections avec les commentaires
-- ==========================================================================

-- ══════════════════════════════════════════════════════════════════════════
-- SECTION 1 : EXPLORATION BASIQUE
-- ══════════════════════════════════════════════════════════════════════════

-- Q01 : Compter les lignes de chaque table (audit volumétrique)
-- SELECT : sélectionne des colonnes ou expressions
-- COUNT(*) : compte TOUTES les lignes (même avec des NULLs)
-- FROM : source de données
-- AS : alias (renommer le résultat)
SELECT COUNT(*) AS nb_clients     FROM customers;
SELECT COUNT(*) AS nb_commandes   FROM orders;
SELECT COUNT(*) AS nb_lignes_cmd  FROM order_items;
SELECT COUNT(*) AS nb_produits    FROM products;

-- Q02 : Aperçu des 10 premières commandes
-- LIMIT : limite le nombre de lignes retournées
-- ORDER BY : trier les résultats
-- DESC : ordre décroissant (plus récent en premier)
SELECT
    order_id,
    customer_id,
    order_date,
    status,
    shipping_fee
FROM orders
ORDER BY order_date DESC
LIMIT 10;

-- Q03 : Structure détaillée d'une commande spécifique
-- WHERE : filtre les lignes selon une condition
-- = : égalité exacte (sensible à la casse en SQLite)
SELECT *
FROM orders
WHERE order_id = 'ORD-536365';

-- Q04 : Distribution des statuts de commande
-- GROUP BY : regrouper les lignes identiques
-- COUNT(*) : compter par groupe
-- ORDER BY COUNT(*) DESC : trier par fréquence décroissante
SELECT
    status,
    COUNT(*) AS nb_commandes,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM orders), 1) AS pourcentage
FROM orders
GROUP BY status
ORDER BY nb_commandes DESC;

-- EXPLICATION de la sous-requête :
-- (SELECT COUNT(*) FROM orders) -> calcule le total une seule fois
-- COUNT(*) * 100.0 -> multiplier par 100.0 (pas 100 !) pour obtenir un REAL
-- 100 (entier) / 12000 (entier) = 0 en division entière !
-- 100.0 (réel) / 12000 = 0.00833... correct

-- Q05 : Clients les plus récents (inscrits en 2023)
-- strftime() -> fonction SQLite pour formater/extraire des dates
-- '%Y' -> extraire l'année
-- BETWEEN : équivalent à >= AND <=
SELECT
    customer_id,
    first_name || ' ' || last_name AS nom_complet,  -- || = concaténation
    email,
    segment,
    signup_date
FROM customers
WHERE strftime('%Y', signup_date) = '2023'
ORDER BY signup_date DESC
LIMIT 15;

-- Q06 : Produits par tranche de prix
-- CASE WHEN ... THEN ... END : expression conditionnelle (comme IF/ELIF)
SELECT
    name,
    unit_price,
    cost_price,
    ROUND(unit_price - cost_price, 2) AS marge_brute,
    ROUND((unit_price - cost_price) / unit_price * 100, 1) AS marge_pct,
    CASE
        WHEN unit_price < 30      THEN 'Entrée de gamme'
        WHEN unit_price < 100     THEN 'Milieu'
        WHEN unit_price < 300     THEN 'Haut de gamme'
        ELSE                           'Premium'
    END AS tranche_prix
FROM products
WHERE is_active = 1
ORDER BY unit_price DESC;

-- Q07 : Statistiques sur les prix des produits
-- MIN, MAX, AVG -> fonctions d'agrégation
-- ROUND(x, 2) -> arrondir à 2 décimales
SELECT
    COUNT(*)                         AS nb_produits,
    ROUND(MIN(unit_price), 2)        AS prix_min,
    ROUND(MAX(unit_price), 2)        AS prix_max,
    ROUND(AVG(unit_price), 2)        AS prix_moyen,
    ROUND(AVG(cost_price), 2)        AS cout_moyen,
    ROUND(AVG(unit_price - cost_price), 2) AS marge_moy,
    ROUND(AVG((unit_price-cost_price)/unit_price*100), 1) AS marge_pct_moy
FROM products
WHERE is_active = 1;

-- Q08 : Clients actifs par segment
-- HAVING : filtre appliqué APRÈS GROUP BY (≠ WHERE qui filtre avant)
-- Règle : WHERE filtre les lignes, HAVING filtre les groupes
SELECT
    segment,
    COUNT(*) AS nb_clients,
    SUM(is_active) AS clients_actifs,
    ROUND(SUM(is_active) * 100.0 / COUNT(*), 1) AS taux_actifs_pct
FROM customers
GROUP BY segment
HAVING COUNT(*) > 10
ORDER BY nb_clients DESC;

-- Q09 : Volume de commandes par mois
-- strftime('%Y-%m', ...) -> extrait Année-Mois (ex: '2022-03')
SELECT
    strftime('%Y-%m', order_date) AS mois,
    COUNT(*)                       AS nb_commandes,
    SUM(CASE WHEN status='Livré' THEN 1 ELSE 0 END) AS livrees,
    SUM(CASE WHEN status='Annulé' THEN 1 ELSE 0 END) AS annulees
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY mois;

-- Q10 : Recherche texte dans les produits
-- LIKE : recherche partielle dans un texte
-- % : joker (n'importe quels caractères)
-- LOWER() : convertir en minuscules pour recherche insensible à la casse
SELECT product_id, name, unit_price
FROM products
WHERE LOWER(name) LIKE '%bluetooth%'
   OR LOWER(name) LIKE '%connecté%'
ORDER BY unit_price DESC;

-- Q11 : Commandes avec frais de port élevés
-- IS NOT NULL : la valeur n'est pas NULL
-- > : supérieur à
SELECT
    order_id,
    order_date,
    status,
    shipping_fee
FROM orders
WHERE shipping_fee > 0
  AND shipping_fee IS NOT NULL
  AND status != 'Annulé'    -- != ou <> = différent de
ORDER BY shipping_fee DESC
LIMIT 20;

-- Q12 : Distribution des notes d'avis clients
SELECT
    rating,
    COUNT(*) AS nb_avis,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM reviews), 1) AS pct
FROM reviews
GROUP BY rating
ORDER BY rating;

-- ══════════════════════════════════════════════════════════════════════════
-- SECTION 2 : REQUÊTES AVANCÉES SUR UNE TABLE
-- ══════════════════════════════════════════════════════════════════════════

-- Q13 : CA par ligne de commande (colonne calculée)
-- Formule : quantité × prix_unitaire × (1 - remise)
SELECT
    item_id,
    order_id,
    product_id,
    quantity,
    unit_price,
    discount_pct,
    ROUND(quantity * unit_price * (1 - discount_pct), 2) AS ca_ligne
FROM order_items
ORDER BY ca_ligne DESC
LIMIT 20;

-- Q14 : Sous-requête : commandes avec CA total > 500€
-- Sous-requête dans le WHERE : calcul par commande, filtre ensuite
SELECT order_id
FROM (
    -- Sous-requête : CA par commande
    SELECT
        order_id,
        SUM(quantity * unit_price * (1 - discount_pct)) AS ca_total
    FROM order_items
    GROUP BY order_id
) AS ca_par_commande
WHERE ca_total > 500
ORDER BY ca_total DESC
LIMIT 10;

-- Q15 : Produits jamais commandés (sous-requête NOT IN)
SELECT product_id, name, unit_price
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id FROM order_items
)
AND is_active = 1;

-- NOT IN : exclure les product_id qui apparaissent dans order_items
-- DISTINCT : éliminer les doublons dans la sous-requête
```

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
5⃣  src/data_loader.py — CHARGEMENT SQL -> PANDAS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

```python
# src/data_loader.py
"""
Module de chargement des données depuis SQL vers pandas.

Responsabilité : exécuter les requêtes SQL et retourner des DataFrames.
Toutes les requêtes sont centralisées ici pour être réutilisables.
"""

import pandas as pd
import logging
from typing import Optional
from src.db_connection import DatabaseConnection

logger = logging.getLogger("DataInsightPro")


class SQLDataLoader:
    """
    Charge les données depuis la base SQL dans des DataFrames pandas.

    Chaque méthode correspond à une question business :
      charger_commandes()   -> toutes les commandes avec détails
      charger_clients()     -> tous les clients avec région
      charger_ca_mensuel()  -> CA agrégé par mois
      etc.
    """

    def __init__(self, chemin_db: str = "data/real_database.db"):
        self.chemin_db = chemin_db
        logger.info(f"SQLDataLoader initialisé -> {chemin_db}")

    def _exec(self, sql: str, params=None) -> pd.DataFrame:
        """Méthode interne : ouvre une connexion, exécute, ferme."""
        with DatabaseConnection(self.chemin_db) as db:
            return db.query_df(sql, params)

    # ─────────────────────────────────────────────────────────────
    # CHARGEMENTS PRINCIPAUX
    # ─────────────────────────────────────────────────────────────

    def charger_commandes_completes(self,
                                     date_debut: str = "2022-01-01",
                                     date_fin: str = "2023-12-31") -> pd.DataFrame:
        """
        Charge toutes les commandes avec informations enrichies.

        Jointures : orders + customers + regions + payment_methods
        Colonnes calculées : ca_commande (sans les lignes de détail)
        """
        sql = """
        SELECT
            o.order_id,
            o.order_date,
            o.status          AS statut,
            o.shipping_fee    AS frais_port,
            c.customer_id,
            c.first_name || ' ' || c.last_name AS client_nom,
            c.segment         AS client_segment,
            c.gender          AS client_genre,
            r.country         AS pays,
            r.region_name     AS region,
            pm.name           AS mode_paiement,
            pm.type           AS type_paiement,
            p.amount          AS montant_paiement,
            p.status          AS statut_paiement
        FROM orders o
        JOIN customers c     ON o.customer_id = c.customer_id
        JOIN regions r       ON o.region_id   = r.region_id
        JOIN payment_methods pm ON o.method_id = pm.method_id
        LEFT JOIN payments p ON o.order_id   = p.order_id
        WHERE o.order_date BETWEEN :d_debut AND :d_fin
        ORDER BY o.order_date
        """
        df = self._exec(sql, {"d_debut": date_debut, "d_fin": date_fin})
        df["order_date"] = pd.to_datetime(df["order_date"])
        logger.info(f"commandes_completes : {len(df):,} lignes")
        return df

    def charger_ca_par_commande(self) -> pd.DataFrame:
        """
        Calcule le CA HT par commande en agrégeant les lignes order_items.
        """
        sql = """
        SELECT
            oi.order_id,
            o.order_date,
            o.status,
            o.customer_id,
            SUM(oi.quantity)                                      AS qte_totale,
            SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)) AS ca_ht,
            AVG(oi.discount_pct)                                  AS remise_moy,
            COUNT(oi.item_id)                                     AS nb_lignes,
            o.shipping_fee
        FROM order_items oi
        JOIN orders o ON oi.order_id = o.order_id
        GROUP BY oi.order_id, o.order_date, o.status, o.customer_id, o.shipping_fee
        ORDER BY o.order_date
        """
        df = self._exec(sql)
        df["order_date"] = pd.to_datetime(df["order_date"])
        df["ca_total"]   = df["ca_ht"] + df["shipping_fee"].fillna(0)
        return df

    def charger_performance_produits(self) -> pd.DataFrame:
        """Performance complète par produit : CA, quantité, marge, avis."""
        sql = """
        SELECT
            p.product_id,
            p.name            AS produit,
            c.name            AS categorie,
            p.unit_price,
            p.cost_price,
            ROUND(p.unit_price - p.cost_price, 2)           AS marge_brute,
            ROUND((p.unit_price-p.cost_price)/p.unit_price*100,1) AS marge_pct,
            p.stock_qty,
            COUNT(oi.item_id)                                AS nb_ventes,
            COALESCE(SUM(oi.quantity), 0)                    AS qte_vendue,
            COALESCE(ROUND(SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)),2), 0) AS ca_total,
            COALESCE(ROUND(AVG(rv.rating), 2), NULL)         AS note_moy,
            COUNT(DISTINCT rv.review_id)                     AS nb_avis
        FROM products p
        JOIN categories c ON p.category_id = c.category_id
        LEFT JOIN order_items oi ON p.product_id = oi.product_id
        LEFT JOIN orders o       ON oi.order_id = o.order_id AND o.status = 'Livré'
        LEFT JOIN reviews rv     ON p.product_id = rv.product_id
        WHERE p.is_active = 1
        GROUP BY p.product_id, p.name, c.name, p.unit_price, p.cost_price, p.stock_qty
        ORDER BY ca_total DESC
        """
        return self._exec(sql)

    def charger_ca_mensuel(self) -> pd.DataFrame:
        """CA mensuel aggrégé pour l'analyse temporelle."""
        sql = """
        SELECT
            strftime('%Y-%m', o.order_date)     AS mois,
            strftime('%Y', o.order_date)        AS annee,
            CAST(strftime('%m', o.order_date) AS INTEGER) AS num_mois,
            COUNT(DISTINCT o.order_id)           AS nb_commandes,
            COUNT(DISTINCT o.customer_id)        AS nb_clients,
            SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)) AS ca_ht,
            SUM(o.shipping_fee)                  AS frais_port_total,
            AVG(oi.discount_pct)                 AS remise_moy
        FROM orders o
        JOIN order_items oi ON o.order_id = oi.order_id
        WHERE o.status != 'Annulé'
        GROUP BY strftime('%Y-%m', o.order_date)
        ORDER BY mois
        """
        df = self._exec(sql)
        df["ca_ht"]    = df["ca_ht"].round(2)
        df["ca_total"] = (df["ca_ht"] + df["frais_port_total"].fillna(0)).round(2)
        return df

    def charger_rfm_clients(self, date_ref: str = "2024-01-01") -> pd.DataFrame:
        """Métriques RFM directement calculées en SQL."""
        sql = """
        SELECT
            c.customer_id,
            c.first_name || ' ' || c.last_name AS nom,
            c.segment,
            r.country,
            r.region_name,
            CAST(julianday(:date_ref) - julianday(MAX(o.order_date)) AS INTEGER)
                                                AS recency_jours,
            COUNT(DISTINCT o.order_id)          AS frequency,
            ROUND(SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)), 2)
                                                AS monetary
        FROM customers c
        JOIN orders o     ON c.customer_id = o.customer_id
        JOIN order_items oi ON o.order_id = oi.order_id
        JOIN regions r    ON c.region_id  = r.region_id
        WHERE o.status != 'Annulé'
          AND c.is_active = 1
        GROUP BY c.customer_id, c.first_name, c.last_name, c.segment, r.country, r.region_name
        ORDER BY monetary DESC
        """
        return self._exec(sql, {"date_ref": date_ref})

    def charger_stats_par_pays(self) -> pd.DataFrame:
        """Performance commerciale par pays."""
        sql = """
        SELECT
            r.country,
            COUNT(DISTINCT c.customer_id)        AS nb_clients,
            COUNT(DISTINCT o.order_id)           AS nb_commandes,
            ROUND(SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)), 2) AS ca_total,
            ROUND(AVG(oi.quantity * oi.unit_price * (1-oi.discount_pct)), 2) AS ca_moyen_ligne,
            ROUND(AVG(rv.rating), 2)             AS note_moy_produits
        FROM regions r
        JOIN customers c ON c.region_id = r.region_id
        JOIN orders o    ON o.customer_id = c.customer_id
        JOIN order_items oi ON oi.order_id = o.order_id
        LEFT JOIN reviews rv ON rv.order_id = o.order_id
        WHERE o.status IN ('Livré', 'En cours')
        GROUP BY r.country
        ORDER BY ca_total DESC
        """
        return self._exec(sql)
```

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[10]  EXERCICES PARTIE 2
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

FACILE :
  Ex 2.1 : Comptez les clients par genre (M/F/Autre) et leur pourcentage.
  Ex 2.2 : Listez les 10 produits avec la plus grande marge brute en €.
  Ex 2.3 : Quel est le mois qui a le plus de commandes ? Le moins ?

INTERMÉDIAIRE :
  Ex 2.4 : Calculez le taux de remise moyen par catégorie (jointure order_items + products + categories).
  Ex 2.5 : Trouvez les clients qui ont passé plus de 10 commandes.
  Ex 2.6 : Quelle est la note moyenne des produits Électronique vs Vêtements ?

AVANCÉ :
  Ex 2.7 : Créez une requête qui retourne, pour chaque mois, le produit
           le plus vendu de ce mois (utiliser une sous-requête corrélée).
  Ex 2.8 : Trouvez les clients dont le CA total a DIMINUÉ entre 2022 et 2023.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
CORRIGÉS PARTIE 2
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

```sql
-- ── Corrigé Ex 2.1 ───────────────────────────────────────────────
SELECT
    gender,
    COUNT(*) AS nb,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM customers), 1) AS pct
FROM customers
GROUP BY gender
ORDER BY nb DESC;

-- ── Corrigé Ex 2.2 ───────────────────────────────────────────────
SELECT
    name,
    unit_price,
    cost_price,
    ROUND(unit_price - cost_price, 2) AS marge_brute_eur
FROM products
WHERE is_active = 1
ORDER BY marge_brute_eur DESC
LIMIT 10;

-- ── Corrigé Ex 2.5 ───────────────────────────────────────────────
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name AS nom,
    c.segment,
    COUNT(o.order_id) AS nb_commandes
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id
HAVING COUNT(o.order_id) > 10
ORDER BY nb_commandes DESC;

-- ── Corrigé Ex 2.8 ─── Clients CA décroissant 2022->2023 ─────────
SELECT
    customer_id,
    ca_2022,
    ca_2023,
    ROUND(ca_2023 - ca_2022, 2) AS variation
FROM (
    SELECT
        customer_id,
        SUM(CASE WHEN annee='2022' THEN ca ELSE 0 END) AS ca_2022,
        SUM(CASE WHEN annee='2023' THEN ca ELSE 0 END) AS ca_2023
    FROM (
        SELECT
            o.customer_id,
            strftime('%Y', o.order_date) AS annee,
            oi.quantity * oi.unit_price * (1 - oi.discount_pct) AS ca
        FROM orders o
        JOIN order_items oi ON o.order_id = oi.order_id
        WHERE o.status != 'Annulé'
    )
    GROUP BY customer_id
)
WHERE ca_2022 > 0 AND ca_2023 < ca_2022
ORDER BY variation ASC
LIMIT 20;
```

================================================================================
  [GRAPHIQUE] DataInsight Pro SQL — PARTIE 3 — JOINTURES SQL COMPLÈTES
================================================================================

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
4⃣  THÉORIE — LES JOINTURES SQL
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

LES 4 TYPES DE JOINTURES :

INNER JOIN (= JOIN)
  -> Retourne les lignes qui ont une correspondance dans LES DEUX tables
  -> La plus courante. Élimine les orphelins des deux côtés.
  -> orders JOIN customers -> seulement les commandes avec client valide

LEFT JOIN (= LEFT OUTER JOIN)
  -> Retourne TOUTES les lignes de la table GAUCHE
  -> + les correspondances de la table droite (NULL si pas de correspondance)
  -> products LEFT JOIN order_items -> TOUS les produits, même ceux jamais vendus

RIGHT JOIN (= RIGHT OUTER JOIN)
  -> Inverse du LEFT JOIN (peu utilisé, préférer LEFT JOIN avec tables inversées)
  -> Non disponible dans certaines versions de SQLite

FULL OUTER JOIN
  -> TOUTES les lignes des deux tables (avec NULL quand pas de correspondance)
  -> Non disponible dans SQLite (simuler avec UNION de deux LEFT JOIN)

CROSS JOIN
  -> Produit cartésien : chaque ligne × chaque ligne
  -> N lignes table A × M lignes table B = N×M lignes résultat
  -> Rarement utile, TRÈS coûteux

VISUALISATION :

Table A (orders)    Table B (customers)
┌─────────────┐     ┌─────────────────┐
│ ORD-1  C-1  │     │ C-1  Alice      │
│ ORD-2  C-2  │     │ C-2  Bob        │
│ ORD-3  C-99 │     │ C-3  Charlie    │
└─────────────┘     └─────────────────┘
C-99 n'existe pas   C-3 n'a pas de commande

INNER JOIN : ORD-1+Alice, ORD-2+Bob        (C-99 et C-3 exclus)
LEFT JOIN  : ORD-1+Alice, ORD-2+Bob, ORD-3+NULL  (C-3 exclu)
FULL OUTER : ORD-1+Alice, ORD-2+Bob, ORD-3+NULL, NULL+Charlie

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
5⃣  REQUÊTES SQL — JOINTURES PROGRESSIVES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

```sql
-- ==========================================================================
-- SECTION 3 : JOINTURES — Du simple au complexe
-- ==========================================================================

-- ── JOINTURE 2 TABLES ─────────────────────────────────────────────────────

-- J01 : Commandes avec informations client (INNER JOIN basique)
-- ON : condition de jointure (correspondance entre les clés)
-- o. et c. : alias de table pour disambiguïser les colonnes
SELECT
    o.order_id,
    o.order_date,
    o.status,
    c.customer_id,
    c.first_name || ' ' || c.last_name AS client,
    c.segment
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'Livré'
ORDER BY o.order_date DESC
LIMIT 20;

-- J02 : Produits avec leur catégorie (INNER JOIN référentiel)
SELECT
    p.product_id,
    p.name       AS produit,
    c.name       AS categorie,
    p.unit_price,
    p.stock_qty
FROM products p
JOIN categories c ON p.category_id = c.category_id
ORDER BY c.name, p.unit_price DESC;

-- J03 : Produits JAMAIS vendus (LEFT JOIN + IS NULL)
-- Technique : si oi.product_id IS NULL -> aucune correspondance dans order_items
SELECT
    p.product_id,
    p.name,
    p.unit_price,
    p.stock_qty
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL   -- IS NULL = pas de correspondance trouvée
  AND p.is_active = 1;

-- ── JOINTURES 3+ TABLES ───────────────────────────────────────────────────

-- J04 : Vue complète d'une commande (4 tables)
SELECT
    o.order_id,
    o.order_date,
    o.status,
    c.first_name || ' ' || c.last_name AS client,
    c.segment,
    r.country,
    r.region_name,
    pm.name    AS paiement,
    p.amount   AS montant_paye,
    p.status   AS statut_paiement
FROM orders o
JOIN customers c     ON o.customer_id = c.customer_id
JOIN regions r       ON o.region_id   = r.region_id
JOIN payment_methods pm ON o.method_id = pm.method_id
LEFT JOIN payments p ON o.order_id   = p.order_id
WHERE o.order_date >= '2022-01-01'
ORDER BY o.order_date DESC
LIMIT 10;

-- J05 : Détail complet des lignes de commande (5 tables)
SELECT
    oi.order_id,
    o.order_date,
    o.status                AS statut_commande,
    c.first_name || ' ' || c.last_name AS client,
    cat.name                AS categorie,
    p.name                  AS produit,
    oi.quantity,
    oi.unit_price,
    oi.discount_pct,
    ROUND(oi.quantity * oi.unit_price * (1 - oi.discount_pct), 2) AS ca_ligne
FROM order_items oi
JOIN orders o    ON oi.order_id  = o.order_id
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p  ON oi.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id
ORDER BY o.order_date DESC, ca_ligne DESC
LIMIT 30;

-- ── ANALYSES BUSINESS AVEC JOINTURES ──────────────────────────────────────

-- J06 : CA total par catégorie (jointure + agrégation)
SELECT
    cat.name                AS categorie,
    COUNT(DISTINCT o.order_id)  AS nb_commandes,
    COUNT(DISTINCT o.customer_id) AS nb_clients,
    SUM(oi.quantity)            AS qte_totale,
    ROUND(SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)), 2) AS ca_total,
    ROUND(AVG(oi.quantity * oi.unit_price * (1-oi.discount_pct)), 2) AS ca_moyen_ligne
FROM categories cat
JOIN products p   ON cat.category_id = p.category_id
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o     ON oi.order_id = o.order_id
WHERE o.status != 'Annulé'
GROUP BY cat.name
ORDER BY ca_total DESC;

-- J07 : Top clients avec leur pays et segment
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name AS client,
    c.segment,
    r.country,
    r.region_name,
    COUNT(DISTINCT o.order_id)  AS nb_commandes,
    ROUND(SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)), 2) AS ca_total,
    ROUND(AVG(rv.rating), 2)    AS note_moy_donnee
FROM customers c
JOIN regions r    ON c.region_id = r.region_id
JOIN orders o     ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN reviews rv ON o.order_id = rv.order_id AND c.customer_id IN (
    SELECT customer_id FROM orders WHERE order_id = rv.order_id
)
WHERE o.status != 'Annulé'
  AND c.is_active = 1
GROUP BY c.customer_id, c.first_name, c.last_name, c.segment, r.country, r.region_name
ORDER BY ca_total DESC
LIMIT 20;

-- J08 : Analyse retours — quels produits sont le plus retournés ?
SELECT
    p.name          AS produit,
    cat.name        AS categorie,
    COUNT(ret.return_id) AS nb_retours,
    SUM(ret.quantity)    AS qte_retournee,
    SUM(ret.refund_amount) AS remboursements_total,
    -- Taux de retour = retours / ventes
    ROUND(COUNT(ret.return_id) * 100.0 /
          NULLIF((SELECT COUNT(*) FROM order_items oi2 WHERE oi2.product_id = p.product_id), 0),
          1) AS taux_retour_pct,
    -- Raison principale
    (SELECT reason FROM returns r2
     WHERE r2.product_id = p.product_id
     GROUP BY reason
     ORDER BY COUNT(*) DESC
     LIMIT 1) AS raison_principale
FROM products p
JOIN categories cat ON p.category_id = cat.category_id
JOIN returns ret    ON p.product_id  = ret.product_id
GROUP BY p.product_id, p.name, cat.name
ORDER BY nb_retours DESC
LIMIT 15;

-- EXPLICATION NULLIF() :
-- NULLIF(x, 0) -> retourne NULL si x=0, sinon retourne x
-- Évite la division par zéro (10 / 0 = erreur en SQL)
-- Si aucune vente d'un produit -> NULLIF retourne NULL -> résultat NULL (pas d'erreur)

-- J09 : Performance par mode de paiement
SELECT
    pm.name         AS mode_paiement,
    pm.type         AS type_paiement,
    COUNT(DISTINCT o.order_id)  AS nb_commandes,
    ROUND(SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)), 2) AS ca_total,
    ROUND(AVG(pay.amount), 2)   AS montant_moyen_transaction,
    SUM(CASE WHEN pay.status = 'Remboursé' THEN 1 ELSE 0 END) AS nb_remboursements
FROM payment_methods pm
JOIN orders o     ON o.method_id  = pm.method_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN payments pay ON pay.order_id = o.order_id
GROUP BY pm.name, pm.type
ORDER BY ca_total DESC;

-- J10 : SELF JOIN — Clients du même pays ayant des comportements similaires
-- (Exemple pédagogique de SELF JOIN sur la table customers)
SELECT
    c1.customer_id AS client_1,
    c2.customer_id AS client_2,
    r.country,
    c1.segment
FROM customers c1
JOIN customers c2 ON c1.region_id = c2.region_id
                  AND c1.segment = c2.segment
                  AND c1.customer_id < c2.customer_id  -- Éviter les doublons
JOIN regions r    ON c1.region_id = r.region_id
WHERE r.country = 'France'
  AND c1.segment = 'Grande entreprise'
LIMIT 10;

-- ── WINDOW FUNCTIONS (Fonctions fenêtres) ────────────────────────────────

-- J11 : Rang des produits par CA dans leur catégorie
-- ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) = rang dans chaque groupe
-- PARTITION BY = définit les groupes (comme GROUP BY mais sans réduire les lignes)
SELECT
    cat.name       AS categorie,
    p.name         AS produit,
    ROUND(SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)), 2) AS ca,
    ROW_NUMBER() OVER (
        PARTITION BY cat.name
        ORDER BY SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)) DESC
    ) AS rang_dans_categorie,
    RANK() OVER (
        ORDER BY SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)) DESC
    ) AS rang_global
FROM products p
JOIN categories cat ON p.category_id = cat.category_id
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o       ON oi.order_id = o.order_id
WHERE o.status != 'Annulé'
GROUP BY cat.name, p.name
ORDER BY cat.name, rang_dans_categorie;

-- J12 : Évolution du CA cumulé mois par mois (running total)
-- SUM(...) OVER (ORDER BY mois ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
SELECT
    strftime('%Y-%m', o.order_date) AS mois,
    ROUND(SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)), 2) AS ca_mensuel,
    ROUND(SUM(SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)))
          OVER (ORDER BY strftime('%Y-%m', o.order_date)), 2) AS ca_cumul
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status != 'Annulé'
GROUP BY strftime('%Y-%m', o.order_date)
ORDER BY mois;

-- ── CTEs (Common Table Expressions) — WITH ────────────────────────────────

-- J13 : CTE — Identifier les clients "Grands Comptes" (CA > 1000€)
-- WITH nom_cte AS (...) -> définir une requête nommée réutilisable
-- Plus lisible que les sous-requêtes imbriquées
WITH ca_clients AS (
    -- CTE 1 : CA par client
    SELECT
        o.customer_id,
        ROUND(SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)), 2) AS ca_total,
        COUNT(DISTINCT o.order_id) AS nb_commandes
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status != 'Annulé'
    GROUP BY o.customer_id
),
grands_comptes AS (
    -- CTE 2 : Filtrer les grands comptes
    SELECT customer_id, ca_total, nb_commandes
    FROM ca_clients
    WHERE ca_total > 1000
)
-- Requête finale : enrichir avec les infos clients
SELECT
    gc.customer_id,
    c.first_name || ' ' || c.last_name AS client,
    c.segment,
    r.country,
    gc.ca_total,
    gc.nb_commandes,
    ROUND(gc.ca_total / gc.nb_commandes, 2) AS panier_moyen
FROM grands_comptes gc
JOIN customers c ON gc.customer_id = c.customer_id
JOIN regions r   ON c.region_id = r.region_id
ORDER BY gc.ca_total DESC
LIMIT 20;
```

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
6⃣  EXPLICATION — WINDOW FUNCTIONS ET CTEs
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

WINDOW FUNCTIONS vs GROUP BY :

  GROUP BY RÉDUIT les lignes :
    5 produits dans "Électronique" -> 1 ligne résultat par catégorie

  WINDOW FUNCTIONS CONSERVENT les lignes :
    5 produits dans "Électronique" -> 5 lignes, chacune avec le rang

ROW_NUMBER() OVER (PARTITION BY cat ORDER BY ca DESC)
  OVER         -> "appliquer sur une fenêtre (partition) des données"
  PARTITION BY -> regrouper (comme GROUP BY mais sans réduire)
  ORDER BY     -> définir l'ordre pour calculer le rang

  ROW_NUMBER -> 1,2,3,4,5 sans ex-aequo
  RANK       -> 1,2,2,4,5 (saute si ex-aequo)
  DENSE_RANK -> 1,2,2,3,4 (ne saute pas si ex-aequo)

CTEs (WITH ... AS (...)) :
  Avantage : nommer des sous-requêtes -> code lisible
  On peut enchaîner plusieurs CTEs :
    WITH cte1 AS (...), cte2 AS (SELECT ... FROM cte1), ...

COALESCE(x, valeur_defaut) :
  Retourne x si x n'est pas NULL, sinon retourne valeur_defaut
  Exemple : COALESCE(discount_pct, 0.0) -> remplace les NULL par 0

NULLIF(x, valeur_a_remplacer) :
  Retourne NULL si x = valeur_a_remplacer, sinon retourne x
  Utilisé pour éviter la division par zéro : x / NULLIF(denom, 0)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
8⃣  BONNES PRATIQUES SQL — PERFORMANCE ET LISIBILITÉ
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

INDEXATION :
  [OK] Index sur les colonnes de jointure (FK)
     CREATE INDEX idx_orders_customer ON orders(customer_id);
  [OK] Index sur les colonnes de filtre (WHERE fréquents)
     CREATE INDEX idx_orders_date ON orders(order_date);
  [OK] Index sur les colonnes de GROUP BY
  [X] Pas d'index sur toutes les colonnes (ralentit les INSERT/UPDATE)

VÉRIFIER UN PLAN D'EXÉCUTION :
  EXPLAIN QUERY PLAN SELECT ... -> affiche comment SQLite traite la requête
  "SCAN TABLE orders" -> séquentiel (lent sur grandes tables)
  "SEARCH TABLE orders USING INDEX" -> utilise l'index (rapide)

FORMAT ET LISIBILITÉ :
  [OK] Mots-clés SQL en MAJUSCULES : SELECT, FROM, WHERE, JOIN...
  [OK] Alias de tables courts et explicites : o pour orders, c pour customers
  [OK] Chaque clause sur sa propre ligne
  [OK] Indenter les sous-requêtes
  [OK] Commenter les requêtes complexes

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
[10]  EXERCICES PARTIE 3
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

FACILE :
  Ex 3.1 : Listez toutes les commandes avec le nom du client et le pays,
           pour les commandes livrées en France.

  Ex 3.2 : Affichez les 5 produits les mieux notés (note_moy >= 4.5)
           avec leur catégorie et leur nombre d'avis.

INTERMÉDIAIRE :
  Ex 3.3 : Créez une requête qui liste pour chaque pays :
           nb_clients, nb_commandes, CA_total, panier_moyen, taux_livraison.

  Ex 3.4 : En utilisant une CTE, trouvez les clients qui n'ont passé
           AUCUNE commande depuis le 1er janvier 2023.

AVANCÉ :
  Ex 3.5 : Utilisez une window function pour calculer le % du CA de chaque
           catégorie par rapport au CA total global.

  Ex 3.6 : Créez une requête de "market basket analysis" simple :
           Quels paires de produits sont souvent achetés ensemble ?
           (Grouper les order_items par order_id, trouver les paires)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
CORRIGÉS PARTIE 3
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

```sql
-- ── Corrigé Ex 3.3 ─── Performance par pays ──────────────────────
SELECT
    r.country,
    COUNT(DISTINCT c.customer_id) AS nb_clients,
    COUNT(DISTINCT o.order_id)    AS nb_commandes,
    ROUND(SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)), 2) AS ca_total,
    ROUND(SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)) /
          NULLIF(COUNT(DISTINCT o.order_id), 0), 2) AS panier_moyen,
    ROUND(SUM(CASE WHEN o.status='Livré' THEN 1 ELSE 0 END) * 100.0 /
          NULLIF(COUNT(DISTINCT o.order_id), 0), 1) AS taux_livraison_pct
FROM regions r
JOIN customers c  ON c.region_id = r.region_id
JOIN orders o     ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY r.country
ORDER BY ca_total DESC;

-- ── Corrigé Ex 3.4 ─── Clients inactifs depuis 2023 ─────────────
WITH derniere_cmd AS (
    SELECT
        customer_id,
        MAX(order_date) AS derniere_commande
    FROM orders
    WHERE status != 'Annulé'
    GROUP BY customer_id
)
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name AS client,
    c.segment,
    dc.derniere_commande
FROM customers c
JOIN derniere_cmd dc ON c.customer_id = dc.customer_id
WHERE dc.derniere_commande < '2023-01-01'
  AND c.is_active = 1
ORDER BY dc.derniere_commande;

-- ── Corrigé Ex 3.6 ─── Market Basket (paires de produits) ────────
SELECT
    a.product_id AS produit_a,
    b.product_id AS produit_b,
    COUNT(*) AS nb_commandes_ensemble,
    pa.name  AS nom_a,
    pb.name  AS nom_b
FROM order_items a
JOIN order_items b ON a.order_id = b.order_id
                   AND a.product_id < b.product_id  -- Évite les doublons
JOIN products pa ON a.product_id = pa.product_id
JOIN products pb ON b.product_id = pb.product_id
GROUP BY a.product_id, b.product_id
HAVING COUNT(*) >= 5
ORDER BY nb_commandes_ensemble DESC
LIMIT 20;
```

================================================================================
FIN PARTIES 2 & 3 — Prochaine : Partie 4 — Nettoyage + EDA en Python+SQL
================================================================================

================================================================================
  [GRAPHIQUE] DataInsight Pro SQL — PARTIE 4 — NETTOYAGE ET QUALITÉ DES DONNÉES SQL
================================================================================

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
5⃣  AUDIT QUALITÉ SQL + src/data_cleaning.py
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

```sql
-- ==========================================================================
-- AUDIT QUALITÉ — Détecter les problèmes dans la base
-- ==========================================================================

-- AQ01 : Valeurs NULL par table et colonne
SELECT 'customers' AS table_name, 'phone' AS colonne,
       COUNT(*) - COUNT(phone) AS nb_null FROM customers
UNION ALL
SELECT 'customers', 'birth_date', COUNT(*) - COUNT(birth_date) FROM customers
UNION ALL
SELECT 'orders', 'notes', COUNT(*) - COUNT(notes) FROM orders
UNION ALL
SELECT 'reviews', 'comment', COUNT(*) - COUNT(comment) FROM reviews
UNION ALL
SELECT 'returns', 'refund_amount', COUNT(*) - COUNT(refund_amount) FROM returns
ORDER BY nb_null DESC;

-- AQ02 : Doublons dans les emails clients
SELECT email, COUNT(*) AS nb_occurrences
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY nb_occurrences DESC;

-- AQ03 : Intégrité référentielle — commandes sans client valide
SELECT COUNT(*) AS orphelins_commandes
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

-- AQ04 : Lignes de commande sans commande parente
SELECT COUNT(*) AS orphelins_order_items
FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_id IS NULL;

-- AQ05 : Dates invalides (format non ISO)
SELECT order_id, order_date
FROM orders
WHERE order_date NOT LIKE '____-__-__'   -- Pattern YYYY-MM-DD
   OR order_date < '2020-01-01'
   OR order_date > '2024-12-31';

-- AQ06 : Prix négatifs ou nuls
SELECT product_id, name, unit_price, cost_price
FROM products
WHERE unit_price <= 0 OR cost_price <= 0
   OR unit_price < cost_price;  -- Prix de vente < coût = perte systématique

-- AQ07 : Clients sans commande (depuis leur inscription)
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name AS client,
    c.signup_date,
    c.segment,
    r.country
FROM customers c
JOIN regions r ON c.region_id = r.region_id
WHERE c.customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders
)
AND c.is_active = 1
ORDER BY c.signup_date;

-- AQ08 : Commandes "Retourné" sans entrée dans la table returns
SELECT o.order_id, o.order_date, o.status, o.customer_id
FROM orders o
LEFT JOIN returns ret ON o.order_id = ret.order_id
WHERE o.status = 'Retourné'
  AND ret.return_id IS NULL;

-- AQ09 : Statistiques de qualité globale
WITH stats AS (
    SELECT
        (SELECT COUNT(*) FROM customers) AS total_clients,
        (SELECT COUNT(*) FROM orders) AS total_commandes,
        (SELECT COUNT(*) FROM order_items) AS total_lignes,
        (SELECT COUNT(*) FROM payments WHERE status = 'Validé') AS paiements_valides,
        (SELECT COUNT(*) FROM reviews) AS total_avis,
        (SELECT COUNT(*) FROM customers WHERE is_active = 1) AS clients_actifs
)
SELECT
    total_clients,
    clients_actifs,
    ROUND(clients_actifs * 100.0 / total_clients, 1) AS pct_actifs,
    total_commandes,
    total_lignes,
    ROUND(total_lignes * 1.0 / total_commandes, 2) AS lignes_par_commande,
    total_avis,
    paiements_valides
FROM stats;
```

```python
# src/data_cleaning.py — Nettoyage Python post-SQL
"""
Nettoyage et validation des DataFrames chargés depuis SQL.
"""
import pandas as pd
import numpy as np
import logging
from src.db_connection import DatabaseConnection

logger = logging.getLogger("DataInsightPro")


class SQLDataCleaner:
    """Nettoie et valide les données extraites de la base SQL."""

    def __init__(self, chemin_db: str = "data/real_database.db"):
        self.chemin_db = chemin_db
        self.journal = []

    def audit_complet(self) -> dict:
        """Rapport d'audit complet via SQL."""
        with DatabaseConnection(self.chemin_db) as db:

            # Compter les lignes par table
            tables = ['customers','orders','order_items','products',
                      'payments','reviews','returns','inventory_log']
            volumetrie = {}
            for t in tables:
                volumetrie[t] = db.query_df(f"SELECT COUNT(*) as n FROM {t}")["n"].iloc[0]

            # NULL par colonne critique
            sql_nulls = """
            SELECT
                SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) AS phone_null,
                SUM(CASE WHEN birth_date IS NULL THEN 1 ELSE 0 END) AS birth_null,
                COUNT(*) as total
            FROM customers
            """
            nulls = db.query_df(sql_nulls).iloc[0]

            # Doublons emails
            n_doublons = db.query_df("""
                SELECT COUNT(*) as n FROM (
                    SELECT email FROM customers
                    GROUP BY email HAVING COUNT(*) > 1
                )
            """)["n"].iloc[0]

            # Avis moyens
            stats_avis = db.query_df("""
                SELECT AVG(rating) as moy, MIN(rating) as min, MAX(rating) as max
                FROM reviews
            """).iloc[0]

        return {
            "volumetrie": volumetrie,
            "null_phone": int(nulls["phone_null"]),
            "null_birth": int(nulls["birth_null"]),
            "doublons_email": int(n_doublons),
            "note_moy": round(float(stats_avis["moy"]), 2),
        }

    def nettoyer_commandes(self, df: pd.DataFrame) -> pd.DataFrame:
        """Nettoie le DataFrame des commandes."""
        df = df.copy()
        n0 = len(df)

        # 1. Convertir les dates
        if "order_date" in df.columns:
            df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
            n_nat = df["order_date"].isna().sum()
            if n_nat > 0:
                logger.warning(f"  {n_nat} dates invalides supprimées")
                df = df[df["order_date"].notna()]

        # 2. Filtrer les dates hors plage 2022-2023
        if "order_date" in df.columns:
            mask = (df["order_date"] >= "2022-01-01") & (df["order_date"] <= "2023-12-31")
            df = df[mask]

        # 3. Remplir les frais de port NaN par 0
        if "frais_port" in df.columns:
            df["frais_port"] = df["frais_port"].fillna(0.0)

        # 4. Normaliser les statuts
        if "statut" in df.columns:
            df["statut"] = df["statut"].str.strip()

        # 5. Colonnes temporelles
        if "order_date" in df.columns:
            df["annee"]     = df["order_date"].dt.year.astype("Int16")
            df["mois"]      = df["order_date"].dt.month.astype("Int8")
            df["trimestre"] = df["order_date"].dt.quarter.astype("Int8")
            df["jour_semaine"] = df["order_date"].dt.dayofweek.astype("Int8")
            df["est_weekend"]  = df["jour_semaine"].isin([5, 6])

        logger.info(f"nettoyer_commandes : {n0} -> {len(df)} lignes")
        return df

    def ajouter_colonnes_derivees(self, df_ca: pd.DataFrame) -> pd.DataFrame:
        """Ajoute les colonnes calculées au DataFrame CA."""
        df = df_ca.copy()
        if "ca_ht" in df.columns and "frais_port_total" in df.columns:
            df["ca_total"] = (df["ca_ht"] + df["frais_port_total"].fillna(0)).round(2)
        if "ca_ht" in df.columns and "nb_commandes" in df.columns:
            df["panier_moyen"] = (df["ca_ht"] / df["nb_commandes"]).round(2)
        if "ca_ht" in df.columns:
            df["variation_pct"] = df["ca_ht"].pct_change() * 100
            df["ma3_ca"] = df["ca_ht"].rolling(3, min_periods=1).mean().round(2)
        return df
```

================================================================================
  [GRAPHIQUE] DataInsight Pro SQL — PARTIE 5 — EDA COMPLET PYTHON + SQL
================================================================================

```python
# src/analysis.py — EDA + KPIs complets depuis SQL
"""
Module d'analyse : combine SQL (agrégations) + pandas (statistiques).
"""
import pandas as pd
import numpy as np
import logging
from scipy import stats
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler
from src.data_loader import SQLDataLoader

logger = logging.getLogger("DataInsightPro")


class AnalystePro:
    """Pipeline d'analyse EuroShop Commerce depuis la base SQL."""

    def __init__(self, chemin_db: str = "data/real_database.db"):
        self.loader = SQLDataLoader(chemin_db)

    def kpis_globaux(self) -> dict:
        """KPIs calculés directement en SQL (performances optimales)."""
        from src.db_connection import DatabaseConnection
        with DatabaseConnection(self.loader.chemin_db) as db:
            sql = """
            SELECT
                COUNT(DISTINCT o.order_id)   AS nb_commandes,
                COUNT(DISTINCT o.customer_id) AS nb_clients,
                COUNT(DISTINCT oi.product_id) AS nb_produits_vendus,
                ROUND(SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)), 2) AS ca_ht,
                ROUND(AVG(oi.quantity * oi.unit_price * (1-oi.discount_pct)), 2) AS ca_moyen_ligne,
                ROUND(SUM(CASE WHEN o.status='Livré' THEN 1.0 ELSE 0 END) /
                      COUNT(*) * 100, 1) AS taux_livraison_pct,
                ROUND(AVG(rv.rating), 2) AS note_moy_globale,
                COUNT(DISTINCT rv.review_id) AS nb_avis,
                ROUND(SUM(ret.refund_amount), 2) AS total_remboursements
            FROM orders o
            JOIN order_items oi ON o.order_id = oi.order_id
            LEFT JOIN reviews rv ON o.order_id = rv.order_id
            LEFT JOIN returns ret ON o.order_id = ret.order_id
            WHERE o.order_date BETWEEN '2022-01-01' AND '2023-12-31'
            """
            return db.query_df(sql).iloc[0].to_dict()

    def rfm_avec_clustering(self, k: int = 4) -> pd.DataFrame:
        """RFM calculé en SQL + clustering K-Means en Python."""
        df_rfm = self.loader.charger_rfm_clients()

        # K-Means sur RFM normalisé
        scaler = StandardScaler()
        rfm_s  = scaler.fit_transform(df_rfm[["recency_jours", "frequency", "monetary"]])
        km = KMeans(n_clusters=k, random_state=42, n_init=20)
        df_rfm["cluster"] = km.fit_predict(rfm_s)

        # Profil des clusters
        profil = df_rfm.groupby("cluster").agg(
            n=("customer_id","count"),
            r=("recency_jours","mean"),
            f=("frequency","mean"),
            m=("monetary","mean"),
        ).round(2)

        def nommer(row):
            r_med = df_rfm["recency_jours"].median()
            f_med = df_rfm["frequency"].median()
            m_med = df_rfm["monetary"].median()
            if row["r"] < r_med and row["f"] >= f_med and row["m"] >= m_med: return "Champions"
            elif row["r"] < r_med and row["f"] >= f_med: return "Fidèles"
            elif row["r"] < r_med: return "Récents"
            elif row["m"] >= m_med: return "Dormants à valeur"
            else: return "Perdus"

        profil["segment"] = profil.apply(nommer, axis=1)
        mapping = profil["segment"].to_dict()
        df_rfm["segment"] = df_rfm["cluster"].map(mapping)
        return df_rfm

    def test_statistique_remises(self, df_ca: pd.DataFrame) -> dict:
        """Test Mann-Whitney : remises vs quantité."""
        if "remise_moy" not in df_ca.columns:
            return {}
        avec = df_ca[df_ca["remise_moy"] > 0]["ca_ht"].dropna()
        sans = df_ca[df_ca["remise_moy"] == 0]["ca_ht"].dropna()
        u, p = stats.mannwhitneyu(avec, sans, alternative="two-sided")
        return {
            "U": round(float(u), 2), "p_value": round(float(p), 6),
            "significatif": p < 0.05,
            "mediane_avec_remise": round(float(avec.median()), 2),
            "mediane_sans_remise": round(float(sans.median()), 2),
        }
```

================================================================================
  [GRAPHIQUE] DataInsight Pro SQL — PARTIE 6 — VISUALISATION
================================================================================

```python
# src/visualization.py — Graphiques complets
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
import seaborn as sns
import pandas as pd
import numpy as np
import logging
from pathlib import Path

logger = logging.getLogger("DataInsightPro")

plt.rcParams.update({
    "figure.dpi": 130, "font.size": 9,
    "axes.spines.top": False, "axes.spines.right": False,
    "axes.grid": True, "grid.alpha": 0.3,
})


class VisualiseurSQL:
    """Génère les graphiques à partir des DataFrames SQL."""

    def __init__(self, dossier: str = "reports"):
        self.dossier = Path(dossier)
        self.dossier.mkdir(exist_ok=True)

    def _save(self, fig, nom):
        p = self.dossier / f"{nom}.png"
        fig.savefig(p, dpi=130, bbox_inches="tight")
        plt.close(fig)
        logger.info(f"  [SAUVEGARDE] {p.name}")

    def dashboard_kpis(self, kpis: dict) -> None:
        """Dashboard KPIs en metric cards."""
        fig = plt.figure(figsize=(16, 3), facecolor="#1F4E79")
        cards = [
            ("CA Total HT", f"{float(kpis.get('ca_ht',0)):,.0f} €"),
            ("Commandes", f"{int(kpis.get('nb_commandes',0)):,}"),
            ("Clients", f"{int(kpis.get('nb_clients',0)):,}"),
            ("Note Moyenne", f"* {float(kpis.get('note_moy_globale',0) or 0):.2f}"),
            ("Taux Livraison", f"{float(kpis.get('taux_livraison_pct',0)):.1f}%"),
            ("Remboursements", f"{float(kpis.get('total_remboursements',0) or 0):,.0f} €"),
        ]
        gs = fig.add_gridspec(1, len(cards), wspace=0.05)
        for i, (label, val) in enumerate(cards):
            ax = fig.add_subplot(gs[0, i])
            ax.set_facecolor("#2E6DA4"); ax.axis("off")
            ax.text(0.5, 0.58, val, ha="center", va="center",
                    fontsize=14, fontweight="bold", color="white", transform=ax.transAxes)
            ax.text(0.5, 0.25, label, ha="center", va="center",
                    fontsize=8, color="#B0C4DE", transform=ax.transAxes)
        fig.suptitle("[GRAPHIQUE] DataInsight Pro SQL — EuroShop Commerce", color="white", fontsize=12, y=1.02)
        self._save(fig, "sql_fig_01_kpis")

    def ca_par_categorie(self, df_prod: pd.DataFrame) -> None:
        """Barres horizontales du CA par catégorie."""
        if df_prod.empty: return
        fig, ax = plt.subplots(figsize=(12, 5))
        cat_ca = df_prod.groupby("categorie")["ca_total"].sum().sort_values(ascending=True)
        n = len(cat_ca)
        colors = [plt.cm.Blues(0.35 + 0.65 * i / max(n-1,1)) for i in range(n)]
        barres = ax.barh(cat_ca.index, cat_ca.values, color=colors, edgecolor="white")
        total = cat_ca.sum()
        for b in barres:
            pct = b.get_width() / total * 100
            ax.text(b.get_width() + total*0.01, b.get_y() + b.get_height()/2,
                    f"{b.get_width():,.0f} € ({pct:.1f}%)", va="center", fontsize=8)
        ax.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"{x/1000:.0f}K€"))
        ax.set_xlim(0, cat_ca.max() * 1.3)
        ax.set_title("CA par Catégorie de Produit")
        plt.tight_layout(); self._save(fig, "sql_fig_02_categories")

    def evolution_temporelle(self, df_mois: pd.DataFrame) -> None:
        """Évolution du CA mensuel."""
        if df_mois.empty: return
        fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(14, 8), sharex=True)

        ax1.bar(range(len(df_mois)), df_mois["ca_ht"], color="#2196F3", alpha=0.6, label="CA mensuel")
        if "ma3_ca" in df_mois.columns:
            ax1.plot(range(len(df_mois)), df_mois["ma3_ca"], "r-", lw=2, label="MA 3 mois")
        ax1.set_ylabel("CA HT (€)")
        ax1.set_title("Évolution CA mensuel")
        ax1.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"{x/1000:.0f}K€"))
        ax1.legend()

        if "variation_pct" in df_mois.columns:
            var = df_mois["variation_pct"].fillna(0)
            colors_v = ["#4CAF50" if v >= 0 else "#F44336" for v in var]
            ax2.bar(range(len(df_mois)), var, color=colors_v, alpha=0.8)
            ax2.axhline(0, color="black", lw=0.8)
            ax2.set_ylabel("Variation MoM (%)")
            ax2.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f"{x:+.0f}%"))
            ax2.set_title("Variation mensuelle (MoM)")

        xticks = list(range(len(df_mois)))
        ax2.set_xticks(xticks[::2])
        ax2.set_xticklabels(df_mois["mois"].iloc[::2], rotation=35, fontsize=7)
        plt.tight_layout(); self._save(fig, "sql_fig_03_temporel")

    def heatmap_pays_categorie(self, df_detail: pd.DataFrame) -> None:
        """Heatmap pays × catégorie."""
        if "pays" not in df_detail.columns or "categorie" not in df_detail.columns: return
        pivot = df_detail.pivot_table(
            values="ca_ligne", index="pays", columns="categorie",
            aggfunc="sum", fill_value=0
        )
        pivot_n = pivot.div(pivot.sum(axis=1), axis=0) * 100
        fig, ax = plt.subplots(figsize=(13, 6))
        sns.heatmap(pivot_n, annot=True, fmt=".0f", cmap="YlOrRd",
                    linewidths=0.4, ax=ax, cbar_kws={"label": "% CA"})
        ax.set_title("Part de CA par Pays et Catégorie (%)")
        ax.set_xticklabels(ax.get_xticklabels(), rotation=30, ha="right", fontsize=7)
        plt.tight_layout(); self._save(fig, "sql_fig_04_heatmap")

    def rfm_segments(self, df_rfm: pd.DataFrame) -> None:
        """Visualisation segments RFM."""
        if "segment" not in df_rfm.columns: return
        fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(14, 6))
        palette = {"Champions":"#FFD700","Fidèles":"#4CAF50",
                   "Récents":"#2196F3","Dormants à valeur":"#FF9800","Perdus":"#9E9E9E"}

        for seg, grp in df_rfm.groupby("segment"):
            ax1.scatter(grp["recency_jours"], grp["monetary"], alpha=0.45, s=18,
                        color=palette.get(seg, "gray"), label=seg)
        ax1.set_xlabel("Récence (jours)")
        ax1.set_ylabel("CA Total (€)")
        ax1.set_title("Récence vs Valeur — Segments RFM")
        ax1.legend(fontsize=8)

        counts = df_rfm["segment"].value_counts()
        ax2.pie(counts.values, labels=counts.index,
                colors=[palette.get(s, "#999") for s in counts.index],
                autopct="%1.1f%%", startangle=90, textprops={"fontsize": 8})
        ax2.set_title("Répartition des segments")
        plt.tight_layout(); self._save(fig, "sql_fig_05_rfm")

    def top_produits_note(self, df_prod: pd.DataFrame) -> None:
        """Scatter CA vs note moyenne des produits."""
        if "note_moy" not in df_prod.columns or "ca_total" not in df_prod.columns: return
        df_plot = df_prod.dropna(subset=["note_moy"])
        fig, ax = plt.subplots(figsize=(12, 7))
        cats = df_plot["categorie"].unique()
        palette = {c: plt.cm.tab10(i/len(cats)) for i, c in enumerate(cats)}

        for cat, grp in df_plot.groupby("categorie"):
            ax.scatter(grp["note_moy"], grp["ca_total"]/1000, alpha=0.7,
                       s=grp["qte_vendue"] * 0.3 + 10,
                       color=palette[cat], label=cat, edgecolors="white", lw=0.3)

        # Annoter les top 10
        for _, row in df_plot.nlargest(10, "ca_total").iterrows():
            ax.annotate(row["produit"][:20], xy=(row["note_moy"], row["ca_total"]/1000),
                        fontsize=6, ha="left")

        ax.set_xlabel("Note Moyenne Client (1-5)")
        ax.set_ylabel("CA Total (K€)")
        ax.set_title("CA vs Note des Produits (taille = quantité vendue)")
        ax.legend(fontsize=7, loc="upper left")
        plt.tight_layout(); self._save(fig, "sql_fig_06_produits_note")
```

================================================================================
  [GRAPHIQUE] DataInsight Pro SQL — PARTIES 7 & 8 — CAS BUSINESS + PROJET FINAL
================================================================================

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
PARTIE 7 — 3 CAS BUSINESS RÉSOLUS EN SQL + PYTHON
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

```sql
-- ==========================================================================
-- CAS BUSINESS 1 — "Quels produits réactiver en stock ?"
-- Le responsable des achats veut identifier :
-- -> Produits à forte demande mais stock < 20 unités
-- -> Produits jamais commandés (à discontinuer)
-- ==========================================================================

-- CB1a : Produits à réapprovisionner en urgence
SELECT
    p.product_id,
    p.name        AS produit,
    c.name        AS categorie,
    p.stock_qty,
    p.unit_price,
    COUNT(oi.item_id) AS nb_ventes_historiques,
    SUM(oi.quantity)  AS qte_vendue_totale,
    ROUND(AVG(rv.rating), 2) AS note_moy,
    CASE
        WHEN p.stock_qty = 0 THEN '[ALERTE] RUPTURE'
        WHEN p.stock_qty < 10 THEN '[ATTENTION] CRITIQUE'
        WHEN p.stock_qty < 20 THEN '[JAUNE] FAIBLE'
        ELSE '[OK] OK'
    END AS statut_stock
FROM products p
JOIN categories c   ON p.category_id = c.category_id
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o       ON oi.order_id = o.order_id AND o.status != 'Annulé'
LEFT JOIN reviews rv     ON p.product_id = rv.product_id
WHERE p.is_active = 1
GROUP BY p.product_id, p.name, c.name, p.stock_qty, p.unit_price
HAVING p.stock_qty < 20 AND COUNT(oi.item_id) > 5
ORDER BY qte_vendue_totale DESC, p.stock_qty ASC;

-- CB1b : Produits à discontinuer (jamais vendus OU mal notés)
WITH ventes AS (
    SELECT product_id, SUM(quantity) AS qte_vendue
    FROM order_items
    JOIN orders USING(order_id)
    WHERE orders.status != 'Annulé'
    GROUP BY product_id
),
avis AS (
    SELECT product_id, AVG(rating) AS note_moy, COUNT(*) AS nb_avis
    FROM reviews
    GROUP BY product_id
)
SELECT
    p.product_id,
    p.name,
    c.name AS categorie,
    COALESCE(v.qte_vendue, 0) AS qte_vendue,
    COALESCE(a.note_moy, 0)   AS note_moy,
    COALESCE(a.nb_avis, 0)    AS nb_avis,
    p.stock_qty,
    CASE
        WHEN v.qte_vendue IS NULL THEN 'Jamais vendu'
        WHEN a.note_moy < 2.5    THEN 'Mal noté'
        WHEN v.qte_vendue < 3    THEN 'Très faible rotation'
    END AS raison_discontinuation
FROM products p
JOIN categories c ON p.category_id = c.category_id
LEFT JOIN ventes v ON p.product_id = v.product_id
LEFT JOIN avis a   ON p.product_id = a.product_id
WHERE p.is_active = 1
  AND (v.qte_vendue IS NULL OR v.qte_vendue < 3 OR a.note_moy < 2.5)
ORDER BY COALESCE(v.qte_vendue, 0) ASC;


-- ==========================================================================
-- CAS BUSINESS 2 — "Analyse des retours : coût et prévention"
-- La responsable qualité veut comprendre les retours.
-- ==========================================================================

-- CB2a : Coût total des retours par catégorie
WITH retours_enrichis AS (
    SELECT
        ret.return_id,
        ret.reason,
        ret.quantity,
        ret.refund_amount,
        ret.return_date,
        p.name           AS produit,
        cat.name         AS categorie,
        p.cost_price,
        oi.unit_price    AS prix_vente
    FROM returns ret
    JOIN products p    ON ret.product_id = p.product_id
    JOIN categories cat ON p.category_id = cat.category_id
    JOIN order_items oi ON ret.order_id = oi.order_id
                       AND ret.product_id = oi.product_id
)
SELECT
    categorie,
    COUNT(*)                 AS nb_retours,
    SUM(quantity)            AS qte_retournee,
    ROUND(SUM(refund_amount), 2) AS remboursements_total,
    ROUND(AVG(refund_amount), 2) AS remboursement_moyen,
    reason                   AS raison_principale
FROM retours_enrichis
GROUP BY categorie
ORDER BY remboursements_total DESC;

-- CB2b : Taux de retour par produit (top 10 retournés)
SELECT
    p.name AS produit,
    cat.name AS categorie,
    COUNT(DISTINCT oi.order_id) AS nb_commandes,
    COUNT(DISTINCT ret.return_id) AS nb_retours,
    ROUND(COUNT(DISTINCT ret.return_id) * 100.0 /
          NULLIF(COUNT(DISTINCT oi.order_id), 0), 2) AS taux_retour_pct,
    ROUND(SUM(ret.refund_amount), 2) AS total_rembourse
FROM products p
JOIN categories cat ON p.category_id = cat.category_id
JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN returns ret ON ret.product_id = p.product_id
                      AND ret.order_id = oi.order_id
GROUP BY p.product_id, p.name, cat.name
HAVING nb_retours > 0
ORDER BY taux_retour_pct DESC
LIMIT 10;


-- ==========================================================================
-- CAS BUSINESS 3 — "Segmentation RFM et valeur des clients par pays"
-- Le directeur marketing demande un ciblage précis.
-- ==========================================================================

-- CB3 : RFM + segment par pays (résumé exécutif)
WITH rfm AS (
    SELECT
        c.customer_id,
        c.segment,
        r.country,
        CAST(julianday('2024-01-01') - julianday(MAX(o.order_date)) AS INTEGER) AS recency,
        COUNT(DISTINCT o.order_id) AS frequency,
        ROUND(SUM(oi.quantity * oi.unit_price * (1-oi.discount_pct)), 2) AS monetary
    FROM customers c
    JOIN regions r ON c.region_id = r.region_id
    JOIN orders o  ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status != 'Annulé' AND c.is_active = 1
    GROUP BY c.customer_id, c.segment, r.country
),
seuils AS (
    SELECT
        AVG(recency)   AS r_med,
        AVG(frequency) AS f_med,
        AVG(monetary)  AS m_med
    FROM rfm
),
rfm_segmente AS (
    SELECT
        rfm.*,
        CASE
            WHEN rfm.recency < s.r_med AND rfm.frequency >= s.f_med AND rfm.monetary >= s.m_med THEN 'Champions'
            WHEN rfm.recency < s.r_med AND rfm.frequency >= s.f_med THEN 'Fidèles'
            WHEN rfm.recency < s.r_med THEN 'Récents'
            WHEN rfm.monetary >= s.m_med THEN 'Dormants à valeur'
            ELSE 'Perdus'
        END AS segment_rfm
    FROM rfm, seuils s
)
SELECT
    country,
    segment_rfm,
    COUNT(*) AS nb_clients,
    ROUND(AVG(monetary), 2) AS ca_moy_client,
    ROUND(SUM(monetary), 2) AS ca_total_segment
FROM rfm_segmente
GROUP BY country, segment_rfm
ORDER BY country, ca_total_segment DESC;
```

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
PARTIE 8 — main.py COMPLET + RAPPORT FINAL
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

```python
# main.py — Pipeline principal DataInsight Pro SQL
"""
Point d'entrée unique : python main.py
Pipeline : DB -> SQL -> pandas -> visualisation -> rapport Markdown
"""

import sys, time, json, traceback
from pathlib import Path
from datetime import datetime

sys.path.insert(0, str(Path(__file__).parent))

from src.utils import configurer_logging, initialiser_dossiers, afficher_section
from src.db_connection import DatabaseConnection
from src.data_loader import SQLDataLoader
from src.data_cleaning import SQLDataCleaner
from src.analysis import AnalystePro
from src.visualization import VisualiseurSQL

CONFIG = {
    "db_path":       "data/real_database.db",
    "dossier_rapports": "reports",
    "dossier_outputs":  "outputs",
    "dossier_logs":     "logs",
}


def main():
    logger = configurer_logging(niveau="INFO")
    initialiser_dossiers(list(CONFIG.values())[1:])
    debut = time.perf_counter()

    print("""
╔══════════════════════════════════════════════════════════════════╗
║   DataInsight Pro SQL — EuroShop Commerce                       ║
╚══════════════════════════════════════════════════════════════════╝
""")

    try:
        # ── Étape 1 : Audit de la base ────────────────────────────
        afficher_section("ÉTAPE 1 — AUDIT DE LA BASE SQL")
        cleaner = SQLDataCleaner(CONFIG["db_path"])
        audit   = cleaner.audit_complet()
        logger.info(f"  Total commandes  : {audit['volumetrie']['orders']:,}")
        logger.info(f"  Total clients    : {audit['volumetrie']['customers']:,}")
        logger.info(f"  Total avis       : {audit['volumetrie']['reviews']:,}")

        with open(Path(CONFIG["dossier_outputs"]) / "audit_sql.json", "w") as f:
            json.dump(audit, f, indent=2, default=str)

        # ── Étape 2 : Chargement SQL ──────────────────────────────
        afficher_section("ÉTAPE 2 — CHARGEMENT DEPUIS SQL")
        loader = SQLDataLoader(CONFIG["db_path"])

        df_commandes = loader.charger_commandes_completes()
        df_commandes = cleaner.nettoyer_commandes(df_commandes)

        df_mensuel   = loader.charger_ca_mensuel()
        df_mensuel   = cleaner.ajouter_colonnes_derivees(df_mensuel)

        df_produits  = loader.charger_performance_produits()
        df_pays      = loader.charger_stats_par_pays()

        logger.info(f"[OK] {len(df_commandes):,} commandes chargées")

        # ── Étape 3 : Analyse ─────────────────────────────────────
        afficher_section("ÉTAPE 3 — ANALYSE")
        analyste = AnalystePro(CONFIG["db_path"])
        kpis     = analyste.kpis_globaux()
        df_rfm   = analyste.rfm_avec_clustering(k=4)

        logger.info(f"  CA Total HT : {float(kpis.get('ca_ht',0)):,.0f} €")
        logger.info(f"  Note moy.   : {float(kpis.get('note_moy_globale',0) or 0):.2f}/5")

        # Exports CSV
        df_produits.to_csv(Path(CONFIG["dossier_outputs"])/"produits.csv", index=False, encoding="utf-8-sig")
        df_rfm.to_csv(Path(CONFIG["dossier_outputs"])/"rfm_clients.csv", index=False, encoding="utf-8-sig")
        df_pays.to_csv(Path(CONFIG["dossier_outputs"])/"stats_pays.csv", index=False, encoding="utf-8-sig")
        df_mensuel.to_csv(Path(CONFIG["dossier_outputs"])/"ca_mensuel.csv", index=False, encoding="utf-8-sig")

        # ── Étape 4 : Visualisation ───────────────────────────────
        afficher_section("ÉTAPE 4 — VISUALISATIONS")
        viz = VisualiseurSQL(CONFIG["dossier_rapports"])
        viz.dashboard_kpis(kpis)
        viz.ca_par_categorie(df_produits)
        viz.evolution_temporelle(df_mensuel)
        viz.rfm_segments(df_rfm)
        viz.top_produits_note(df_produits)

        if "pays" in df_commandes.columns and "categorie" in df_commandes.columns:
            viz.heatmap_pays_categorie(df_commandes)

        # ── Étape 5 : Rapport Markdown ────────────────────────────
        afficher_section("ÉTAPE 5 — RAPPORT FINAL")
        generer_rapport(kpis, df_produits, df_pays, df_rfm, df_mensuel, audit, CONFIG)

        # ── Résumé ────────────────────────────────────────────────
        duree = time.perf_counter() - debut
        print(f"""
╔══════════════════════════════════════════════════════════════════╗
║   [OK] PIPELINE SQL TERMINÉ EN {duree:.1f}s                              ║
╠══════════════════════════════════════════════════════════════════╣
║   CA Total HT      : {float(kpis.get('ca_ht',0)):>12,.0f} €                  ║
║   Commandes        : {int(kpis.get('nb_commandes',0)):>12,}                        ║
║   Clients          : {int(kpis.get('nb_clients',0)):>12,}                        ║
║   Note moyenne     : {float(kpis.get('note_moy_globale',0) or 0):>12.2f} / 5               ║
║                                                                  ║
║   [DOSSIER] reports/final_report_sql.md                                ║
║   [DOSSIER] outputs/*.csv                                              ║
║   [DOSSIER] reports/sql_fig_*.png                                      ║
╚══════════════════════════════════════════════════════════════════╝
""")
        return 0

    except FileNotFoundError as e:
        logger.error(f"[X] {e}")
        return 1
    except Exception as e:
        logger.error(f"[X] Erreur : {e}")
        logger.error(traceback.format_exc())
        return 2


def generer_rapport(kpis, df_prod, df_pays, df_rfm, df_mois, audit, config):
    """Génère le rapport Markdown final."""

    top3_prod = df_prod.head(3)
    top3_pays = df_pays.head(3)

    ca_2022 = df_mois[df_mois["annee"] == "2022"]["ca_ht"].sum() if "annee" in df_mois else 0
    ca_2023 = df_mois[df_mois["annee"] == "2023"]["ca_ht"].sum() if "annee" in df_mois else 0
    growth = (ca_2023 - ca_2022) / ca_2022 * 100 if ca_2022 > 0 else 0

    segs = df_rfm["segment"].value_counts().to_dict() if "segment" in df_rfm else {}

    rapport = f"""# Rapport Analytique SQL — EuroShop Commerce 2022-2023

> **Auteur :** DataInsight Pro SQL Pipeline
> **Généré le :** {datetime.now().strftime('%d/%m/%Y à %H:%M')}
> **Base de données :** {config['db_path']} ({audit['volumetrie']['orders']:,} commandes)

---

## [GRAPHIQUE] KPIs Globaux

| Indicateur | Valeur |
|---|---|
| **CA Total HT** | {float(kpis.get('ca_ht',0)):,.0f} € |
| **Commandes** | {int(kpis.get('nb_commandes',0)):,} |
| **Clients Uniques** | {int(kpis.get('nb_clients',0)):,} |
| **Note Moyenne** | {float(kpis.get('note_moy_globale',0) or 0):.2f} / 5 |
| **Taux de Livraison** | {float(kpis.get('taux_livraison_pct',0)):.1f}% |
| **Total Remboursements** | {float(kpis.get('total_remboursements',0) or 0):,.0f} € |

---

## [TROPHEE] Top 3 Produits par CA

| Produit | Catégorie | CA Total | Note |
|---|---|---|---|
"""
    for _, r in top3_prod.iterrows():
        rapport += f"| **{r['produit'][:40]}** | {r['categorie']} | {r['ca_total']:,.0f} € | *{r.get('note_moy', 'N/A')} |\n"

    rapport += f"""
---

## [MONDE] Performance Géographique

| Pays | Clients | Commandes | CA Total |
|---|---|---|---|
"""
    for _, r in df_pays.head(6).iterrows():
        rapport += f"| **{r['country']}** | {int(r['nb_clients']):,} | {int(r['nb_commandes']):,} | {r['ca_total']:,.0f} € |\n"

    rapport += f"""
---

## [HAUSSE] Évolution Temporelle

| Exercice | CA HT |
|---|---|
| 2022 | {ca_2022:,.0f} € |
| 2023 | {ca_2023:,.0f} € ({growth:+.1f}%) |

---

## [UTILISATEURS] Segmentation RFM

| Segment | Clients |
|---|---|
"""
    for seg, n in segs.items():
        rapport += f"| **{seg}** | {n:,} ({n/len(df_rfm)*100:.1f}%) |\n"

    rapport += f"""
---

## [LIEN] Architecture SQL

La base comporte **11 tables relationnelles** interconnectées :
- `orders` (12 000 commandes) <- table pivot
- `order_items` (26 063 lignes de commande)
- `customers` (2 500 clients)
- `products` (64 produits), `categories` (8)
- `payments`, `reviews`, `returns`, `inventory_log`

---

## [IDEE] Recommandations

1. **Réapprovisionner** les produits à stock < 20 unités et forte rotation
2. **Relancer** les clients "Dormants à valeur" (segment RFM) par campagne email
3. **Investiguer** les produits à taux de retour > 10%
4. **Cibler** la France et l'Allemagne pour les campagnes B2B (PME)

---

*Pipeline reproductible : `python main.py`*
*Source SQL : `data/real_database.db` — Inspiré UCI Online Retail II*
"""
    chemin = Path(config["dossier_rapports"]) / "final_report_sql.md"
    chemin.write_text(rapport, encoding="utf-8")
    import logging
    logging.getLogger("DataInsightPro").info(f"[OK] Rapport : {chemin}")


if __name__ == "__main__":
    sys.exit(main())
```

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
EXERCICES FINAUX — NIVEAU AVANCÉ
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Ex F1 : Créez une VIEW SQL "vue_ca_complet" qui combine orders + order_items
        + products avec le CA par ligne pré-calculé. Utilisez-la dans toutes
        vos requêtes suivantes au lieu de répéter le calcul.

Ex F2 : Créez un TRIGGER qui met à jour automatiquement products.stock_qty
        à chaque INSERT dans order_items (décrémente le stock).

Ex F3 : Implémentez une analyse de cohorte complète en SQL :
        Pour chaque "cohorte" (mois d'acquisition du client),
        calculez le CA généré chaque mois suivant.
        Affichez sous forme de heatmap en Python.

Ex F4 : PROJET FINAL — Créez une application Streamlit qui :
        1. Connecte à la base SQLite en temps réel
        2. Affiche les KPIs avec mise à jour dynamique
        3. Permet de filtrer par pays, catégorie, période
        4. Exporte les résultats filtrés en CSV

Ex F5 : Migrez la base SQLite vers PostgreSQL :
        - Adaptez DatabaseConnection pour PostgreSQL (psycopg2)
        - Testez que toutes les requêtes fonctionnent (attention aux différences SQL)
        - Comparez les performances sur des requêtes complexes

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
RÉSUMÉ COMPLET DES 8 PARTIES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

PARTIE 1 — Setup + schéma BDD (11 tables, 57 293 lignes, ERD, types SQL, FK)
PARTIE 2 — Requêtes SQL fondamentales (SELECT, WHERE, GROUP BY, HAVING, LIKE)
PARTIE 3 — Jointures (INNER, LEFT, SELF JOIN, CTEs, Window Functions)
PARTIE 4 — Nettoyage SQL + Python (audit, NULL, doublons, intégrité)
PARTIE 5 — EDA Python+SQL (KPIs, RFM, clustering, tests statistiques)
PARTIE 6 — Visualisation (6 graphiques, dashboard, heatmap, RFM scatter)
PARTIE 7 — 3 cas business (stock, retours, segmentation client par pays)
PARTIE 8 — Projet final complet (main.py orchestrateur + rapport Markdown)

CONCEPTS SQL MAÎTRISÉS :
  SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
  INNER JOIN, LEFT JOIN, SELF JOIN
  Sous-requêtes (IN, NOT IN, EXISTS, corrélées)
  CTEs (WITH ... AS)
  Window Functions (ROW_NUMBER, RANK, SUM OVER)
  Fonctions : ROUND, COUNT, SUM, AVG, MIN, MAX, COALESCE, NULLIF
  Dates : strftime(), julianday()
  CASE WHEN ... THEN ... END
  Indexation, PRAGMA, EXPLAIN QUERY PLAN

================================================================================
FIN DU PROJET SQL — DataInsight Pro SQL (8/8 parties complètes)
================================================================================